Hi, I guess the id_file_set is an INT? The problem si most likely due to the fact you are comparing integer to string, which forces MySQL to use type conversion. For more information check http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html
When type conversion occurs MySQL will not be able to use the index and will have to do a full table scan which can be seen from your explain queries. As for the 'bogus' case most likely it has been dropped because it cannot be converted to integer. This case is explained at http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Hope this helps. Regards Dobromr Velev On Wednesday 18 February 2009 05:25, Daevid Vincent wrote: > 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_i >n > > 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 | > +----+-------------+----------------+-------+---------------+-------------+ >---------+------+------+-------------+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
