Bug #8662 | optimizer, query takes 3 to 10 times as long when using index | ||
---|---|---|---|
Submitted: | 21 Feb 2005 21:31 | Modified: | 30 Nov 2009 17:25 |
Reporter: | Martin Friebe (Gold Quality Contributor) (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
Version: | 6.0.14, 5.1.12-BK, 4.1.10 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | qc |
[21 Feb 2005 21:31]
Martin Friebe
[16 Sep 2006 10:43]
Valeriy Kravchuk
Thank you for a problem report. I think, it is not a feature request. If, without index used, query runs much faster (8+ times faster in my case), and this can be proved also with: mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) mysql> select count(c) from j1 left join j2 ignore index (b) on a=b where a > 2 and b < 19 and length(c) <101 limit 2; +----------+ | count(c) | +----------+ | 60000 | +----------+ 1 row in set (0.65 sec) mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) mysql> select count(c) from j1 left join j2 on a=b where a > 2 and b < 19 and l ength(c) <101 limit 2; +----------+ | count(c) | +----------+ | 60000 | +----------+ 1 row in set (5.40 sec) then it is serious bug in optimizer, affecting performance. Verified with latest 5.1.12-BK!
[19 Oct 2006 1:52]
Igor Babaev
I had the following results: mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.26-debug | +--------------+ 1 row in set (0.00 sec) mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) mysql> select count(c) from j1 left join j2 ignore index (b) on a=b where a > 2 and b < 19 and length(c) <101; +----------+ | count(c) | +----------+ | 60000 | +----------+ 1 row in set (1.05 sec) mysql> show status like "Last%"; +-----------------+----------------+ | Variable_name | Value | +-----------------+----------------+ | Last_query_cost | 1602172.309547 | +-----------------+----------------+ 1 row in set (0.01 sec) mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) mysql> select count(c) from j1 left join j2 on a=b where a > 2 and b < 19 and length(c) <101; +----------+ | count(c) | +----------+ | 60000 | +----------+ 1 row in set (3.05 sec) mysql> show status like "Last%"; +-----------------+---------------+ | Variable_name | Value | +-----------------+---------------+ | Last_query_cost | 480002.340797 | +-----------------+---------------+ 1 row in set (0.00 sec) So really a faster execution plan is estimated as more expensive. This is another evidence that the current cost model is far from being perfect. Yet we can't change the cost model in the current versions. We'll try to fix the problem in 5.2.
[30 Nov 2009 17:25]
Valeriy Kravchuk
Re-verified with recent mysql-6.0-codebase: 77-52-7-73:6.0-codebase openxs$ bin/mysql -uroot test Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 6.0.14-alpha-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) mysql> alter table j2 add index (b); Query OK, 40000 rows affected (0.98 sec) Records: 40000 Duplicates: 0 Warnings: 0 mysql> select count(c) from j1 left join j2 ignore index (b) on a=b where a > 2 and b < 19 and length(c) <101; +----------+ | count(c) | +----------+ | 60000 | +----------+ 1 row in set (0.63 sec) mysql> show status like "Last%"; +-----------------+----------------+ | Variable_name | Value | +-----------------+----------------+ | Last_query_cost | 1602543.403297 | +-----------------+----------------+ 1 row in set (0.00 sec) mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) mysql> select count(c) from j1 left join j2 on a=b where a > 2 and b < 19 and length(c) -> <101; +----------+ | count(c) | +----------+ | 60000 | +----------+ 1 row in set (2.61 sec) mysql> show status like "Last%"; +-----------------+---------------+ | Variable_name | Value | +-----------------+---------------+ | Last_query_cost | 480002.340797 | +-----------------+---------------+ 1 row in set (0.00 sec) We still have this bug even in the latest optimizer (as cost model had not really changed notably).