Re: Why do quotes in an IN() clause effect performance so drastically?
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
Re: Why do quotes in an IN() clause effect performance so drastically?
Hi David,
On Wed, Feb 18, 2009 at 4:25 AM, 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 |
> ++-++--+---+--+-+--+-+-+
>
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
Why do quotes in an IN() clause effect performance so drastically?
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 |
++-++---+---+-+-+--+--+-+
