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 f开发者_JAVA百科ind 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.

---

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

---

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

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