I have a MySQL InnoDB database running on the Google App-Engine. One of the tables has the current date and a user_id as primary key stored with some additional data. The table had around 7 million rows and I deleted 6 million of them with a DELETE query. Since that any query using this table is much slower than before.
Any ideas what could cause this behavior or how to solve this?
Thanks in advance!
After such a massive delete on innodb you would better to use OPTIMISE table statement
Use OPTIMIZE TABLE in these cases, depending on the type of table:
After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with
the innodb_file_per_table option enabled. The table and indexes are
reorganized, and disk space can be reclaimed for use by the operating system.After doing substantial insert, update, or delete operations on columns that are part of a FULLTEXT index in an InnoDB table. Set the configuration option innodb_optimize_fulltext_only=1 first. To keep
the index maintenance period to a reasonable time, set the
innodb_ft_num_word_optimize option to specify how many words to
update in the search index, and run a sequence of OPTIMIZE TABLE
statements until the search index is fully updated.
Prior to optimize, check the table's state using ANALYSE TABLE, and it's indexes using SHOW INDEX. These instructions will provide you with information regarding the "flaws" that OPTIMIZE can fix.
All this is easy to do in phpmyadmin.