Hi,

Try enclosing the value in quotes. The primary key is CHAR() column, not an integer column.

When I use quotes, it comes up with a different issue:

mysql> explain SELECT * FROM route WHERE route_id = '30809866';
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- ---------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- ---------------+
1 row in set (0.00 sec)


One thing I noticed is that if I don't quote the route_id value and use an order by clause it seems to use the index correctly:

mysql> explain SELECT * FROM route WHERE route_id = 30809866 order by route_id;
+----+-------------+-------+-------+---------------+-------- -+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------- -+---------+------+--------+-------------+
| 1 | SIMPLE | route | index | PRIMARY | PRIMARY | 30 | NULL | 356424 | Using where |
+----+-------------+-------+-------+---------------+-------- -+---------+------+--------+-------------+
1 row in set (0.00 sec)

The explain plan says that the value is not in the table, thus "impossible where".

mysql> explain SELECT * FROM route WHERE route_id = '30809866';
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- ---------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- ---------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------------------------------- ---------------+
1 row in set (0.00 sec)

During the "const" conversion, MySQL realises that the value '30809866' isn't in the table. The reason is, that a "SELECT *" with an equality in the WHERE clause on the PRIMARY KEY will get converted into "const" access, which means that all the values in the row get turned into constants (this is great for joins).

Your second query will range scan the primary key (due to the ORDER BY) and only return the rows that match (which will be no rows).

Read the EXPLAIN section of the manual and pay particular attention to 'const' access method:
http://dev.mysql.com/doc/refman/5.1/...in-output.html

The plan with the impossible where clause is optimal. It will be a CONST lookup on the PK if the value being looked up is actually in the table.

That makes sense. Thanks Justin.

-Carlos

Quote:

Your second query will range scan the primary key (due to the ORDER BY) and only return the rows that match (which will be no rows).

The primary key is the table itself. Both with and without ORDER BY, the entire table will be read. The query with WHERE route_id = 30809866 can return many rows.

The problem is that you are comparing a string with an integer. Evaluation of the clause WHERE route_id = 30809866 will convert route_id to an integer before performing the comparison. Many different strings will equal 30809866 after conversion. Moreover, you will run into problems because the numbers you use can be larger than the maximum size of an integer, resulting in the use of (inaccurate) floats. Either use integers and use an integer field, or switch to char(10) and use strings in your query. E.g. if you know that route_id's are padded with zeros, use WHERE route_id = '0030809866'