Forum Moderators: coopster
Well, my server is showing 4.2M, which I assume means 4.2 million. My question is: does stepping through a result set with a WHILE $row = mysql_fetch_array($result) count towards this total? I imagine it does, just wanting to make sure. If it does count, is there a way to separate it out so I can see which queries are not working as they should and what in this stat is just stepping through results?
- Ryan
Number of requests to read the next row in the datafile. This will be high if you are doing a lot of table scan
What that says to me is that this isn't the fetch_next_row causing it, but is a count of the number of times that the next row of a table was fetched to return the query. If the query used an index, you wouldn't necessarily expect to see a lot of these, because the DB would be fetching specific rows rather than just the next one.
If you turn on query logging, it will log all queries that required a table scan, or that took over a certain length of time to run (2s by default, I think). This is a better way to find out what query is causing the problem.
Just remember that table scans aren't always bad. Even if the table is indexed, the optimiser may decide that it's faster just to do the table scan rather than consulting an index. It could be that it thinks it will need most of the rows anyway, or that there aren't enough rows to make it worthwhile using an index. The slow log above tells you how many rows were consulted which helps figure that out.
Once you've got your trouble queries identified, you can use the explain command to tell you how it's going to run the query, which will help you optimise it.
Sean
