StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL query not taking advantage of index
text
Body
copied!<p>I was analizing a query (working on a wordpress plugin named nextgen gallery), this is what I got</p> <p>query:</p> <pre><code>EXPLAIN SELECT title, filename FROM wp_ngg_pictures wnp LEFT JOIN wp_ngg_gallery wng ON wng.gid = wnp.galleryid GROUP BY wnp.galleryid LIMIT 5 </code></pre> <p>result:</p> <pre><code>+----+-------------+-------+--------+---------------+---------+---------+-----------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+------+---------------------------------+ | 1 | SIMPLE | wnp | ALL | NULL | NULL | NULL | NULL | 439 | Using temporary; Using filesort | | 1 | SIMPLE | wng | eq_ref | PRIMARY | PRIMARY | 8 | web1db1.wnp.galleryid | 1 | | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+------+---------------------------------+ </code></pre> <p>so I do:</p> <pre><code>ALTER TABLE wp_ngg_pictures ADD INDEX(galleryid); </code></pre> <p>and on my local test system I get:</p> <pre><code>+----+-------------+-------+--------+---------------+-----------+---------+--------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+-----------+---------+--------------------+------+-------+ | 1 | SIMPLE | wnp | index | galleryid | galleryid | 8 | NULL | 30 | | | 1 | SIMPLE | wng | eq_ref | PRIMARY | PRIMARY | 8 | test.wnp.galleryid | 1 | | +----+-------------+-------+--------+---------------+-----------+---------+--------------------+------+-------+ </code></pre> <p>which seems fine, but on the final server I get</p> <pre><code>+----+-------------+-------+--------+---------------+-----------+---------+-----------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+-----------+---------+-----------------------+------+-------+ | 1 | SIMPLE | wnp | index | galleryid | galleryid | 8 | NULL | 439 | | | 1 | SIMPLE | wng | eq_ref | PRIMARY | PRIMARY | 8 | web1db1.wnp.galleryid | 1 | | +----+-------------+-------+--------+---------------+-----------+---------+-----------------------+------+-------+ </code></pre> <p>so the index is used but all the rows are scanned anyway? Why is this happening?</p> <p>Only difference I can see is mysql version which is 5.1.47 (local) vs 5.0.45 (remote), data is the same on both systems.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload