MySQL query not taking advantage of index

Question

I was analizing a query (working on a wordpress plugin named nextgen gallery), this is what I got

query:

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

result:

+----+-------------+-------+--------+---------------+---------+---------+-----------------------+------+---------------------------------+
| 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 |                                 | 
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+------+---------------------------------+

so I do:

ALTER TABLE wp_ngg_pictures ADD INDEX(galleryid);

and on my local test system I get:

+----+-------------+-------+--------+---------------+-----------+---------+--------------------+------+-------+
| 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 |       |
+----+-------------+-------+--------+---------------+-----------+---------+--------------------+------+-------+

which seems fine, but on the final server I get

+----+-------------+-------+--------+---------------+-----------+---------+-----------------------+------+-------+
| 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 |       | 
+----+-------------+-------+--------+---------------+-----------+---------+-----------------------+------+-------+

so the index is used but all the rows are scanned anyway? Why is this happening?

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.

Answer 1

The rows column in the EXPLAIN SELECT output is an estimate of the number of rows that MySQL believes it must examine to execute the query, so I guess it is possible that your local version (5.1.47) is better at estimating than your remote version.

Without the EXPLAIN clause, do both queries produce the same output? What happens if you change the query to use a STRAIGHT_JOIN?