Search Results for: explain rows count

How rows_sent can be more than rows_examined?

When looking at queries that are candidates for optimization I often recommend that people look at rows_sent and rows_examined values as available in the slow query log (as well as some other places). If rows_examined is by far larger than rows_sent, say 100 larger, then the query is a great candidate for optimization. Optimization could […]

Percona Toolkit collection: pt-visual-explain

This is the first in a series of posts highlighting a few of the seldom-used but still handy Percona Toolkit tools. Have you ever had a problem understanding the EXPLAIN statement output? And are you the type of person who would rather use the command line than a GUI application? Then I would recommend that […]

When EXPLAIN estimates can go wrong!

I have been working with a few customer cases and one interesting case popped up. The customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the […]

Explaining Indexes with a Library Metaphor

My favorite metaphor for explaining indexes is comparing them to index cards in an old library. In an old library, you used to (or still do) have index cards at the front desk which have some brief description of the books in the library. They also used to be categorized alphabetically. (image taken from http://www.flickr.com/photos/reedinglessons/2239767394/) […]

Using index for ORDER BY vs restricting number of rows.

One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site which sell goods, goods may be from different categories, different sellers different locations which can be filtered on, and there […]