MySQL Bugs: #18656: User variable in where subquery causes long delay

Bug #18656	User variable in where subquery causes long delay
Submitted:	30 Mar 2006 16:30	Modified:	26 Dec 2010 14:00
Reporter:	adkap	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.0.15	OS:	Linux (Suse 10)
Assigned to:		CPU Architecture:	Any

Description:
In one of my scripts, I was defining MySQL variables early on so that the static values could be reused in later queries without complex sub-queries.  The problem is that if I reference a user variable in a subquery, the execution time of that query will consistantly take SIGNIFICANTLY longer to get a result.

Here is an example of one of the problematic query sequences:

SELECT @SERVER:=serverId FROM Server WHERE name='New Windows Server';
+-------------------+
| @SERVER:=serverId |
+-------------------+
|                72 |
+-------------------+
1 row in set (0.00 sec)

SELECT * FROM ServerStats WHERE serverId=@SERVER AND timestamp=(SELECT MAX(timestamp) FROM ServerStats WHERE serverId=@SERVER);
1 row in set (4.66 sec)

These tables are very small. 4.66s is quite a long time. Here is what happens if the @SERVER is replaced with its static value (72):

SELECT * FROM ServerStats WHERE serverId=@SERVER AND timestamp=(SELECT MAX(timestamp) FROM ServerStats WHERE serverId=72);
1 row in set (0.01 sec)

The same thing happens if @SERVER is explicitely set (SET @SERVER='72').  The faster result is not due to caching because I can execute the slow one multiple times and it is always 4.66s.

Also, executing that whole variable query as the subquery is ironically much faster:

SELECT * FROM ServerStats WHERE serverId=@SERVER AND timestamp=(SELECT MAX(timestamp) FROM ServerStats WHERE serverId=(SELECT serverId FROM Server WHERE name='New Windows Server'));
1 row in set (0.01 sec)

Sorry is this is already filed somewhere... I could not find a bug like this.

How to repeat:
Reference a user variable in the where clause  of a where subquery.

Suggested fix:
Fix the interpeter??

Thank you for a problem report. Please, try to repeat on the newer version, 5.0.19, and inform about the results.

Please, send aslo the results of:

SHOW CREATE TABLE ServerStats;

EXPLAIN SELECT * FROM ServerStats WHERE serverId=@SERVER AND timestamp=(SELECT
MAX(timestamp) FROM ServerStats WHERE serverId=@SERVER)\G

EXPLAIN SELECT * FROM ServerStats WHERE serverId=@SERVER AND timestamp=(SELECT
MAX(timestamp) FROM ServerStats WHERE serverId=72)\G

Note: Some of the columns names that are not applicable were removed.  It's not my place to publish parts of our proprietary schema. ;)

mysql> SHOW CREATE TABLE ServerStats\G
*************************** 1. row ***************************
       Table: ServerStats
Create Table: CREATE TABLE `ServerStats` (
  `na1` bigint(20) NOT NULL auto_increment,
  `na2` int(11) NOT NULL,
  `na3` int(11) default NULL,
  `timestamp` datetime default NULL,
  `na4` datetime default NULL,
  `na5` int(11) default NULL,
  `na6` int(11) default NULL,
  `na7` int(11) default NULL,
  `na8` int(11) default NULL,
  `na9` int(11) default NULL,
  `na10` int(11) default NULL,
  `na11` int(11) default NULL,
  `na12` int(11) default NULL,
  `na13` int(11) default NULL,
  `na14` int(11) default NULL,
  `na15` int(11) default NULL,
  `na16` int(11) default NULL,
  `na17` int(11) default NULL,
  `na18` int(11) default NULL,
  `na19` int(11) default NULL,
  `na20` int(11) default NULL,
  `na21` smallint(6) default '0',
  `serverId` bigint(20) NOT NULL,
  PRIMARY KEY  (`na1`),
  KEY `FK_ServerStats_Server` (`serverId`),
  CONSTRAINT `FK_ServerStats_Server` FOREIGN KEY (`serverId`) REFERENCES `Server` (`serverId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

----------------------------------------------------------

mysql> EXPLAIN SELECT * FROM ServerStats WHERE serverId=@SERVER AND timestamp=(SELECT MAX(timestamp) FROM ServerStats WHERE serverId=@SERVER)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: ServerStats
         type: ref
possible_keys: FK_ServerStats_Server
          key: FK_ServerStats_Server
      key_len: 8
          ref: const
         rows: 896
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: UNCACHEABLE SUBQUERY
        table: ServerStats
         type: ref
possible_keys: FK_ServerStats_Server
          key: FK_ServerStats_Server
      key_len: 8
          ref: const
         rows: 896
        Extra:
2 rows in set (0.00 sec)

----------------------------------------------------------

mysql> EXPLAIN SELECT * FROM ServerStats WHERE serverId=@SERVER AND timestamp=(SELECT MAX(timestamp) FROM ServerStats WHERE serverId=72)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: ServerStats
         type: ref
possible_keys: FK_ServerStats_Server
          key: FK_ServerStats_Server
      key_len: 8
          ref: const
         rows: 896
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: ServerStats
         type: ref
possible_keys: FK_ServerStats_Server
          key: FK_ServerStats_Server
      key_len: 8
          ref:
         rows: 896
        Extra:
2 rows in set (0.00 sec)

If you do need the colum names, I can post a private comment. Also, I can't upgrade this server right now. Though, its only 0.0.04 versions behind v5.0.19.  The table has grown to  900 rows now, and the slow subquery is now over 7 seconds. The fast one is still almost instant.

Query plans are really different with user variable and without it. Have you seen that UNCACHEABLE SUBQUERY in a plan with user varaible? Hance the difference in time. Try to execute both queries with query cache disabled. 

Try to upgrade to a newer version, 5.0.20a also. That 0.0.5 really makes difference.

Please, send also the SHOW TABLE STATUS results for the `ServerStats` table.

the database is empty right now. as soon as it is back online I will provide that information.

Please, reopen this report when you'll have the information required.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I know this is an old issue but as I came across it yesterday in 5.0.77, I will add my test case.

CREATE TABLE test_table (
   id int unsigned NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO test_table VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

CREATE TABLE test_table_2 LIKE test_table;

INSERT INTO test_table_2 SELECT * FROM test_table;

EXPLAIN
 SELECT *
   FROM test_table t1
        JOIN test_table_2 t2 ON t2.id = (SELECT 1
                                           FROM test_table t1_i
                                          WHERE t1_i.id = 1
                                        );

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | t2    | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
|  1 | PRIMARY     | t1    | index | NULL          | PRIMARY | 4       | NULL  |   10 | Using index |
|  2 | SUBQUERY    | t1_i  | const | PRIMARY       | PRIMARY | 4       |       |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.00 sec)

SET @TEST = 1;

EXPLAIN
 SELECT *
   FROM test_table t1
        JOIN test_table_2 t2 ON t2.id = (SELECT 1
                                           FROM test_table t1_i
                                          WHERE t1_i.id = @TEST
                                        );

+----+----------------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type          | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+----------------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|  1 | PRIMARY              | t1    | index | NULL          | PRIMARY | 4       | NULL  |   10 | Using index              |
|  1 | PRIMARY              | t2    | index | NULL          | PRIMARY | 4       | NULL  |   10 | Using where; Using index |
|  2 | UNCACHEABLE SUBQUERY | t1_i  | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index              |
+----+----------------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
3 rows in set (0.00 sec)

So with a user variable the index look up is replaced by a table scan (or in this case an index scan).

I don't know whether this is related to bug #32341?

Let's say this is a duplicate of Bug #32341.