MySQL queries slow after a DELETE

Question

I have a two part PHP script.

1. The first that deletes some rows in the database
2. The second that triggers SELECT queries

The second script alone runs in about 0.2 sec. When both parts are processed, the second part takes 5 sec. The next time the second script runs alone, it's back to 0.2 sec.

Any clue?

Answer 1

Its likely that the query has cached so it runs faster the second time, more info here. If you add SQL_NO_CACHE to the query then you should be able to determine if the cache is a factor.

Answer 2

Your Query-Cache for a table is 'invalidated' each time the table is updated.

The result is that any UPDATE/DELETE/INSERT to your table, will clear the query-cache of that table, and force a fresh disk read for the next SELECT. The result will be a slower query.

Here's a link to the MySQL DOCs.

If your table is incredibly large, you may want to investigate MyISAM tables and a separate Key-Cache for yet better read performance. MyISAM = fast reads, fast writes, horrible concurrency for writes/udpates. InnoDB = mediocre speed for reads, good concurrency for writes/updates.

-- J Jorgenson --