| Bug #16504 | Some of the simplest SELECTs are 1000 times slower in v5 compared to v4 | ||
|---|---|---|---|
| Submitted: | 14 Jan 2006 13:53 | Modified: | 2 Jun 2006 21:48 |
| Reporter: | Frederic Steinfels | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.21 | OS: | Any (*) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[14 Jan 2006 16:25]
MySQL Verification Team
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: This optimisation problem is theoretically solvable with index merge. MySQL can not do index merge in your case, due to the reasons cited in our manual.
[15 Jan 2006 2:37]
Frederic Steinfels
I do not get it. How can this be not a bug when it was working perfectly in v4 and no longer in v5? v5 is said to have the better opimizer but it seems to have the contrary effect.
[18 Jan 2006 0:08]
Frederic Steinfels
Instead of just closing the bug you could have given some useful response to my claim. It took me several hours compiling that information and you just press the f.y. button? Reporting bugs to you is ridicilous, sorry.
[18 Jan 2006 8:29]
Valeriy Kravchuk
Sorry, but it is how things work now. I'll mark this report as (verified) feature request: to improve plan for the queries like these, with OR. It can be done using "index merge" (but column `specialprice` must be indexed for that!) or by rewriting (internally, by optimizer) the query with OR into the equivalent with UNION. But please, do not expect for this feature to be implemented really soon.
[18 Jan 2006 14:47]
Frederic Steinfels
Your statement makes sense if you disregard the fact that that feature in the internal optimizer was present in 4.1 and has been destroyed in 5.x by accident or for whatever reason. Therefore I'd rather call this bug than feature request. Furthermore the 5.x optimizer is said to be much better but unfortunately the contrary seems to be the case for this type of statements. There are various other bugs in this database claiming v5 is much slower but I think my report is the most accurate because it only involves what is really causing the delay and has a complete testcase. Just to remind you we are not talking about a statement that became half as fast due to new features, we are talking about something that takes almost a second to execute where it was 0.00 seconds in v4.
[23 Jan 2006 19:45]
Sergei Golubchik
We need to investigate why the execution plan is different in 4.1 and 5.0. Why 5.0 doesn't use range here ? Query: SELECT pro.sku, pro.name, pro.specialprice, pri.sku, pri.price FROM products as pro, prices as pri WHERE pro.sku='99999362842624' AND (pro.sku=pri.sku OR pro.specialprice=pri.sku); 4.1: +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | pro | const | PRIMARY | PRIMARY | 255 | const | 1 | | | 1 | SIMPLE | pri | range | PRIMARY | PRIMARY | 255 | NULL | 2 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 5.0: +----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ | 1 | SIMPLE | pro | const | PRIMARY | PRIMARY | 255 | const | 1 | | | 1 | SIMPLE | pri | ALL | PRIMARY | NULL | NULL | NULL | 99554 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
[1 Apr 2006 5:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4389
[1 Apr 2006 7:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4390
[1 Apr 2006 7:34]
Igor Babaev
The bug can be demonstrated with a simple test case:
mysql> CREATE TABLE t1 (sku int PRIMARY KEY, pr int);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));
Query OK, 0 rows affected (0.28 sec)
mysql>
mysql> INSERT INTO t1 VALUES
-> (10, 10), (20, 10), (30, 20), (40, 30), (50, 10), (60, 10);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO t2 VALUES
-> (10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
-> (50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
-> FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
+-----+------+------+-----+------+
| sku | sppr | name | sku | pr |
+-----+------+------+-----+------+
| 20 | 10 | bbb | 10 | 10 |
| 20 | 10 | bbb | 20 | 10 |
+-----+------+------+-----+------+
2 rows in set (0.00 sec)
mysql> EXPLAIN
-> SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
-> FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | t1 | ALL | PRIMARY | | | | 6 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
+--------------+
| version() |
+--------------+
| 5.0.21-debug |
+--------------+
1 row in set (0.04 sec)
[4 Apr 2006 4:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4429
[4 Apr 2006 17:22]
Igor Babaev
ChangeSet 1.2125 06/03/31 21:26:17 igor@rurik.mysql.com +9 -0 Fixed bug #16504. Multiple equalities were not adjusted after reading constant tables. It resulted in neglecting good index based methods that could be used to access of other tables. ChangeSet 1.2126 06/03/31 23:12:05 igor@rurik.mysql.com +1 -0 Added a test case for bug #16504. Results changed after the bug fix. ChangeSet 1.2127 06/04/03 21:02:40 igor@rurik.mysql.com +3 -0 Post review changes for the fix of bug #16504. The fix will appear in 5.0.21. It was merged into 5.1.
[4 Apr 2006 20:53]
Gregert Johnson
Congratulations to Frederic Steinfels for his persistence!
[11 Apr 2006 17:55]
Mike Hillyer
Need 5.1 merge version for changelog.
[12 Apr 2006 4:35]
Igor Babaev
It was merged into 5.1.10
[14 Apr 2006 14:09]
Paul DuBois
Noted in 5.0.21, 5.1.10 changelogs. The presence of multiple equalities in a condition after reading a constant table could cause the optimizer not to use an index. This resulted in certain queries being much slower than in MySQL 4.1. (Bug #16504)
[12 May 2006 6:53]
Frederic Steinfels
Thanks for fixing but unfortunately the fix was either not successful or there is much more worse with 5.1. My test case still executes the UNION statement in 0.00 seconds and the statement using OR in 0.20 seconds so there is still something broken. I will do some more investigation as well with MySQL 4.1 and and other statements and reopen the bug if I do gather more evidence. (I have upgraded to 5.0.21 of course)
[12 May 2006 7:07]
Frederic Steinfels
Your MySQL connection id is 108909 to server version: 5.0.21 The changes made to mysql did not fix anything (or at least not this particular bug), the index is still not used in 5.0 mysql> EXPLAIN SELECT pro.sku, pro.name, pro.specialprice, pri.sku, pri.price FROM products as pro, prices as pri WHERE pro.sku=@m AND (pro.sku=pri.sku OR pro.specialprice=pri.sku); +----+-------------+-------+------+---------------+---------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+-------+-------------+ | 1 | SIMPLE | pro | ref | PRIMARY | PRIMARY | 257 | const | 1 | Using where | | 1 | SIMPLE | pri | ALL | PRIMARY | NULL | NULL | NULL | 99555 | Using where | +----+-------------+-------+------+---------------+---------+---------+-------+-------+-------------+ 2 rows in set (0.00 sec) and yes Gregert Johnson, it seems my persistance is required ;-)
[12 May 2006 7:11]
Frederic Steinfels
sorry, typo
[2 Jun 2006 13:03]
Valeriy Kravchuk
Sorry, but it looks like this bug is really fixed. Verified with latest 5.0.23-BK on Linux:
mysql> CREATE TABLE t1 (sku int PRIMARY KEY, pr int);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (10, 10), (20, 10), (30, 20), (40, 30), (50, 10),
(60, 10);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 VALUES
-> (10, 10, 'aaa'), (20, 10, 'bbb'), (30, 10, 'ccc'), (40, 20, 'ddd'),
-> (50, 10, 'eee'), (60, 20, 'fff'), (70, 20, 'ggg'), (80, 30, 'hhh');
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
-> FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku);
+-----+------+------+-----+------+
| sku | sppr | name | sku | pr |
+-----+------+------+-----+------+
| 20 | 10 | bbb | 10 | 10 |
| 20 | 10 | bbb | 20 | 10 |
+-----+------+------+-----+------+
2 rows in set (0.01 sec)
mysql> EXPLAIN EXTENDED
-> SELECT t2.sku, t2.sppr, t2.name, t1.sku, t1.pr
-> FROM t2, t1 WHERE t2.sku=20 AND (t2.sku=t1.sku OR t2.sppr=t1.sku)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 2
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.23 |
+-----------+
1 row in set (0.01 sec)
Index on t2 is used.
Description: A simple select on two tables is no problem. But when trying to fetch two rows using an OR, the select breaks completely and is getting incredibly slow. There are two tests: One using UNION, the other using OR. In real life, UNION is not practical compared to OR because the whole statement has to be repeated. There is a line call testme(100000); It is creating 100000 random entries in the database. Maybe by increasing this you will get more than 0.00secs execution time on the UNION statement version but even if you round up 0.00 to 0.01, it is 80 times faster than the OR statment version (0.80secs). The speed test is done twice in order to show the impact of the cache. In order to compare on mysqld 4.1, create the tables in v4, copy the files to v5, fill them there using the procedure supplied, copy them back to v4. You will see that v4 is executing both statements in 0.00secs!!! How to repeat: DROP TABLE prices; CREATE TABLE prices (sku varchar(255), price varchar(255), PRIMARY KEY(sku)); DROP TABLE products; CREATE TABLE products (sku varchar(255), specialprice varchar(255), name varchar(255), PRIMARY KEY(sku)); DROP PROCEDURE testme; DELIMITER // CREATE PROCEDURE testme(IN c INT) BEGIN DECLARE r FLOAT; WHILE c > 0 DO SET r=SUBSTRING(RAND(),3); INSERT IGNORE INTO `products` SET sku=r,name=r; INSERT IGNORE INTO `prices` SET sku=r,price=r; SET c=c-1; END WHILE; END // DELIMITER ; call testme(100000); SET query_cache_type=OFF; SET @m=(SELECT max(sku) FROM products); SET @s=(SELECT min(sku) FROM products); UPDATE products SET specialprice=@s WHERE sku=@m; SELECT "This is the first DB Query using 'UNION' instaed of 'OR'. Execution time is very small!"; (SELECT pro.sku, pro.name, pro.specialprice, pri.sku, pri.price FROM products as pro, prices as pri WHERE pro.sku=@m AND pro.sku=pri.sku ) UNION ( SELECT pro.sku, pro.name, pro.specialprice, pri.sku, pri.price FROM products as pro, prices as pri WHERE pro.sku=@m AND pro.specialprice=pri.sku ); SELECT "This is the second DB Query using 'OR' instaed of 'UNION'. Execution time is catastrophically high on Mysql 5.0 but not on 4.1!"; SELECT pro.sku, pro.name, pro.specialprice, pri.sku, pri.price FROM products as pro, prices as pri WHERE pro.sku=@m AND (pro.sku=pri.sku OR pro.specialprice=pri.sku); SELECT "The statement using 'UNION' will speed up massively if used a second time even if query cache is disabled!"; (SELECT pro.sku, pro.name, pro.specialprice, pri.sku, pri.price FROM products as pro, prices as pri WHERE pro.sku=@m AND pro.sku=pri.sku ) UNION ( SELECT pro.sku, pro.name, pro.specialprice, pri.sku, pri.price FROM products as pro, prices as pri WHERE pro.sku=@m AND pro.specialprice=pri.sku ); SELECT "The statement using 'OR' won't speed up at all!"; SELECT pro.sku, pro.name, pro.specialprice, pri.sku, pri.price FROM products as pro, prices as pri WHERE pro.sku=@m AND (pro.sku=pri.sku OR pro.specialprice=pri.sku); Suggested fix: Although this is a performance issue, total page rendering time in my web shop would increase from 1 second to 20 seconds for example if showing a decent amount of products and prices on the same page. Therefore MySQL 5 is no good for production until this bug is fixed. Please fix this bug so that mysql 5.0 has decent execution times on this problem.