CODEWITHSUNDEEP
mysqlperformance
Laura
Laura

Reputation: 71

Mysql Find command has become very slow

Each of my web requests involved couple of 'select' queries. Table in question recently went from 75,000 rows to ~90,000 and the select command slowed from taking ~100ms to ~1.2s

What is the best way to find the reason of sudden performance drop? I imagine that key can not be stored in memory and this is causing the drop. How to check for that?

Please advise.

Upvotes: 1

Views: 234

Answers (2)

staticsan
staticsan

Reputation: 30555

It sounds like MySQL is shifting an internal table from memory to disk. This is a classic "tipping-point" with MySQL where performance drops off a cliff once something reaches a certain size. This is especially so when you realize that the default MySQL configuration is extremely conservative (even the so-called "huge" config) and several server settings can be increased by at least an order of magnitude.

The first avenue of exploration is to use EXPLAIN on your query and look for indications it is putting temporary table information on disk. More information is available in the MySQL docs here and here

Upvotes: 3

duffymo
duffymo

Reputation: 308813

EXPLAIN PLAN on the query. Look for a TABLE SCAN.

If you find one, add sufficient indexes to eliminate the problem.

Upvotes: 1

Related Questions