I'm really confused. First, I don't understand why quoting my IN()
values here caused them to run significantly slower than the non-quoted
versions... on just this simple contrived example it can be as much as
2.2 seconds vs. 0 seconds to return on a table that has 2.5M rows.
The problem I'm facing is that the stupid PEAR::DB class is
"smart-quoting" a list of values and giving me this:
mysql> explain select * from bite_event_log where id_file_set in
('-1','2412948') limit 1;
+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | bite_event_log | ALL | id_file_set | NULL | NULL
| NULL | 1213328 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
But what I really want is for it to do this:
mysql> explain select * from bite_event_log where id_file_set in
(-1,2412948) limit 1;
+----+-------------+----------------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | bite_event_log | range | id_file_set |
id_file_set | 5 | NULL | 2 | Using where |
+----+-------------+----------------+-------+---------------+-------------+---------+------+------+-------------+
Mixing quoted and non-quoted is said to be "bad"
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in
mysql> explain select * from bite_event_log where id_file_set in
('-1',2412948) limit 1;
+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | bite_event_log | ALL | id_file_set | NULL | NULL
| NULL | 1213328 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
However, aside from the straight numerical one above (2nd down), this
version is the second best performing!?
And furthermore, using a word string like "bogus" significantly
out-performs another string such as "-1". Huh?!? WTF?
It's like mySQL was "smart enough" to know that "bogus" could be
dropped, whereas it's not smart enough to know to drop "-1",
despite the fact that the id_file_set column is an unsigned integer.
mysql> explain select * from bite_event_log where id_file_set in
('bogus',2412948) limit 1;
+----+-------------+----------------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | bite_event_log | range | id_file_set |
id_file_set | 5 | NULL | 2 | Using where |
+----+-------------+----------------+-------+---------------+-------------+---------+------+------+-------------+
