Where's my index - Fixing webpage slow load times
How do we check if MySQL is slowing down because of missing or incorrect indexes.Web page slow times are one of the most common issues a web developer has to deal with. There are a lot of choices out there when it comes to frameworks, databases, application servers and web servers. Which combination do you choose? Usually any combination runs fine in the beginning. The developer is happy because he got the job done fast. And the client is happy because he got the job done cheap. Uh... I mean fast. Things start going down south pretty soon as the site starts to receive more traffic and the database starts to grow.
Where's my database column indexIt is often said that an application is as complex as big its data is. When your database tables start to grow in row size your application starts to behave a lot differently. A query that used to complete in a 0.001s soon starts to degrade in performance and takes up half a second to complete. Pretty soon you realize that you missed to create a few database indexes. That's usually a quick fix but how do you check if indexes are really the cause for your slow load times.
Percentage of full table scans to the rescueThere are a few mysql performance tunning tools out there that can tell you how often MySQL needs to make a full table scan to answer your queries. One of the best tools I know that do this is MONyog MySQL Monitor. If you are looking for a freee alternative you can try the Percona toolkit.
Here's how to do this manually... Go to mysql console and execute the following command to get a report about few variables.
SHOW global status LIKE "Handler_read%";
Try running this command a few times and observe which variable tends to increase the most. If you want to reset the variables so that they start counting from 0 again you need to run FLUSH STATUS;
So to summarize you need to flush the variables, read them, wait 10 seconds, and read them again. This will give you a snapshot of how the queries running on your MySQL instance used indexes for the last 10 seconds. Here's how to read the resulting variable values:
HANDLER_READ_FIRSTThis variable is increased every time a query runs that needs to access the first entry from an index. It usually means a full index scan was needed. The good news is that an index is being used. The bad news is that there's probably no LIMIT in the query and the whole index is being read every time. A query that increases this variable might look like this:
HANDLER_READ_KEYThis variable is increased every time a query needs to fetch a row by an index value. If this variable is increasing fast that's usually a good thing. It means that indexes are being used often. A query that increases this variable might look like this:
WHERE id = 123
HANDLER_READ_NEXTThis variable is increased every time a query needs to fetch the next row in index key order. It means that you have a query that operates on a range of rows in an ascending key order. Or a query that is doing a full index scan. A query that increases this variable might look like this:
WHERE id BETWEEN 10 AND 20
ORDER BY id ASC;
HANDLER_READ_PREVThis variable is increased every time a query needs to fetch the previous row in index key order. It means that you have a query that operates on a range of rows in an descending key order. Usually those are the queries that have ORDER BY ... DESC
WHERE id BETWEEN 10 AND 20
ORDER BY id DESC;