Hi David,
On Wed, Feb 18, 2009 at 4:25 AM, Daevid Vincent <dae...@daevid.com> 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 |
> +----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
>
Here the quotes are forcing MySQL to see strings where it should see
integers, so when the optimizer evaluates the available indexes it
misses id_file_set index.
> 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 |
> +----+-------------+----------------+-------+---------------+-------------+---------+------+------+-------------+
>
Here the integers are evaluated as integers and the index in used.
> 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 |
> +----+-------------+----------------+------+---------------+------+---------+------+---------+-------------+
This may differ from the original quoted version because the
statistics change or the results are cached, but the explain output is
largely the same.
>
> 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 |
> +----+-------------+----------------+-------+---------------+-------------+---------+------+------+-------------+
>
>
Not sure whats going on here, I am guessing that 'bogus' is cast at some point.
Ewen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
