# Optimizer problems?

**URL:** <https://forums.percona.com/t/optimizer-problems/4218>\
**Category:** Other MySQL® Questions\
**Created:** [May 12, 2015, 9:33am UTC](https://forums.percona.com/t/optimizer-problems/4218 "2015-05-12T09:33:18Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tuple](https://avatars.discourse-cdn.com/v4/letter/t/da6949/32.png) [@tuple](https://forums.percona.com/u/tuple)\
**Post date:** [May 12, 2015, 9:33am UTC](https://forums.percona.com/t/optimizer-problems/4218/1 "2015-05-12T09:33:18Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![ShahriyarR](https://avatars.discourse-cdn.com/v4/letter/s/87869e/32.png) [@ShahriyarR](https://forums.percona.com/u/ShahriyarR)\
**Post date:** [June 24, 2015, 1:14am UTC](https://forums.percona.com/t/optimizer-problems/4218/2 "2015-06-24T01:14:54Z")

</div>

what is exact problem here?

```auto
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)

```

```auto
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:

```auto
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()](https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_min), [MAX()](https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_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](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]