Not the answer you need?
Register and ask your own question!

Optimizer problems?

tupletuple EntrantCurrent User Role Participant
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)

Comments

  • ShahriyarRShahriyarR Contributor Inactive User Role Advisor
    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:
    • Select tables optimized away
      The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*), and no GROUP BY clause. The optimizer determined that only one row should be returned.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.