| Bug #64567 | Last_query_cost is not updated when executing an unique key lookup | ||
|---|---|---|---|
| Submitted: | 6 Mar 2012 18:38 | Modified: | 13 Oct 2018 16:10 |
| Reporter: | Sadao Hiratsuka (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.5.20, 5.5.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, Optimizer, qc | ||
[6 Mar 2012 18:47]
Valeriy Kravchuk
Thank you for the bug report and fix contributed. Verified with older 5.5.20 also: macbook-pro:5.5 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You 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 21 Server version: 5.5.20-debug Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table t; ERROR 1051 (42S02): Unknown table 't' mysql> CREATE TABLE t (id INT PRIMARY KEY, data VARCHAR(10)); Query OK, 0 rows affected (0.20 sec) mysql> INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c'); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t WHERE id = 2; +----+------+ | id | data | +----+------+ | 2 | b | +----+------+ 1 row in set (0.07 sec) mysql> SHOW LOCAL STATUS like 'Last_query_cost'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 0.000000 | +-----------------+----------+ 1 row in set (0.29 sec) mysql> SELECT * FROM t WHERE id < 2; +----+------+ | id | data | +----+------+ | 1 | a | +----+------+ 1 row in set (0.06 sec) mysql> SHOW LOCAL STATUS like 'Last_query_cost'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 1.409000 | +-----------------+----------+ 1 row in set (0.00 sec)
[13 Oct 2018 16:10]
Knut Anders Hatlen
This bug was fixed in 5.6.7 as a side effect of the fix for bug#13111584.
Description: Last_query_cost is not updated when executing an unique key lookup. How to repeat: mysql> CREATE TABLE t (id INT PRIMARY KEY, data VARCHAR(10)); Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO t VALUES (1, 'a'), (2, 'b'), (3, 'c'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t WHERE id = 2; +----+------+ | id | data | +----+------+ | 2 | b | +----+------+ 1 row in set (0.02 sec) mysql> SHOW LOCAL STATUS like 'Last_query_cost'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | Last_query_cost | 0.000000 | +-----------------+----------+ 1 row in set (0.05 sec) In this case, actual SQL cost (by join->best_read) is 1.0. But status_var.last_query_cost is not updated, so this variable remains 0.0. Suggested fix: *** sql_select.cc_org 2012-03-07 03:27:41.869271468 +0900 --- sql_select.cc 2012-03-07 03:28:14.970750000 +0900 *************** *** 3106,3111 **** --- 3106,3112 ---- memcpy((uchar*) join->best_positions,(uchar*) join->positions, sizeof(POSITION)*join->const_tables); join->best_read=1.0; + join->thd->status_var.last_query_cost= join->best_read; } /* Generate an execution plan from the found optimal join order. */ DBUG_RETURN(join->thd->killed || get_best_combination(join));