All geek questions in one place

MySQL queries slow after DELETE

I have two parts of a PHP script.

  • The first one that deletes some rows in the database
  • The second that raises SELECT queries

Only the second script runs after about 0.2 seconds. When both parts are processed, the second part takes 5 seconds. The next time the second script runs alone, it returns to 0.2 sec.

Any clue?

+4
source share
2 answers

It is likely that the request was cached, so it works faster the second time, more details here . If you add SQL_NO_CACHE to the query, you can determine if the cache is a factor.

+1
source

Your cache request for the table is "invalid" every time the table is updated.

As a result, any UPDATE / DELETE / INSERT in your table will clear the query cache of this table and force the new disk to read the next SELECT. The result will be a slow query.

Here is a link to MySQL DOCs .

If your table is incredibly large, you may need to examine MyISAM tables and a separate Key-Cache to improve read performance. MyISAM = fast read, fast write, terrible concurrency for write / udpates. InnoDB = mediocre read speed, good concurrency for writing / updating.

- J Jorgenson -

0
source

Source: https://habr.com/ru/post/1316325/

More articles:


All Articles