Optimizer problems?

[tuple]

Here’s a little mystery - I’ll let the code speak for itself:

mysql> select max(id) from mytable;
±----------+
| max(id) |
±----------+
| 577711424 |
±----------+
1 row in set (0.00 sec)

mysql> select now(), created from mytable where id in (577711424);
±--------------------±--------------------+
| now() | created |
±--------------------±--------------------+
| 2015-05-12 12:04:53 | 2015-05-12 12:03:24 |
±--------------------±--------------------+
1 row in set (0.00 sec)

Now for the kicker:

mysql> select now(), created from mytable where id in (select max(id) from mytable);
±--------------------±--------------------+
| now() | created |
±--------------------±--------------------+
| 2015-05-12 12:05:10 | 2015-05-12 12:05:10 |
±--------------------±--------------------+
1 row in set (6 min 18.60 sec)

mysql> explain select now(), created from mytable where id in (select max(id) from mytable);
±—±-------------------±------±-----±--------------±-----±--------±-----±----------±-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±-------------------±------±-----±--------------±-----±--------±-----±----------±-----------------------------+
| 1 | PRIMARY | mytable | ALL | NULL | NULL | NULL | NULL | 292257304 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
±—±-------------------±------±-----±--------------±-----±--------±-----±----------±-----------------------------+
2 rows in set (0.00 sec)

[ShahriyarR]

what is exact problem here?

mysql> explain select now(), date from news where id in (388294)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: news
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra: NULL
1 row in set (0.03 sec)

mysql> explain select max(id) from news\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
1 row in set (0.00 sec)

Similar to your query:

mysql> explain select now(), date from news where id in (select max(id) from news)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: news
type: index
possible_keys: NULL
key: tarix
key_len: 4
ref: NULL
rows: 261490
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
2 rows in set (0.07 sec)

Documentation portion:
[LIST]
[]Select tables optimized away
[I][B]The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or [URL=“https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_count”]COUNT()[/URL], and no GROUP BY clause. The optimizer determined that only one row should be returned.[/B][/I]
[/LIST]