Tweeted twitter.com/StackDBAs/status/909637255366660096
3 edited tags
| link
2 add create table
source | link

Update: Here are the create table statements:

mysql> SHOW CREATE TABLE readings\G
*************************** 1. row ***************************
       Table: readings
Create Table: CREATE TABLE `readings` (
  `time` int(11) NOT NULL,
  `boxsn` varchar(16) NOT NULL,
  `rev` varchar(16) NOT NULL,
  `schema` tinyint(3) unsigned NOT NULL,
  `interval` smallint(5) unsigned NOT NULL,
  `relay` tinyint(4) NOT NULL,
  `inputV` decimal(10,6) NOT NULL,
  `inputA` decimal(10,6) NOT NULL,
  `outputV` decimal(10,6) NOT NULL,
  `outputA` decimal(10,6) NOT NULL,
  `phase` tinyint(4) NOT NULL,
  `outputVA` decimal(10,6) NOT NULL,
  `watts` decimal(10,6) NOT NULL DEFAULT '0.000000',
  `var` decimal(10,6) NOT NULL,
  `kiloVAHours` decimal(9,9) DEFAULT '0.000000000',
  `kilowattHours` decimal(9,9) NOT NULL,
  `kilovarHours` decimal(9,9) NOT NULL,
  PRIMARY KEY (`boxsn`,`time`),
  KEY `time` (`time`),
  KEY `boxsn_time_ndx` (`boxsn`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE boards\G
*************************** 1. row ***************************
       Table: boards
Create Table: CREATE TABLE `boards` (
  `id` varchar(16) NOT NULL,
  `siteId` int(11) NOT NULL,
  `groupId` int(11) DEFAULT '0',
  `lastReport` int(11) DEFAULT NULL,
  `lastIp` varchar(15) DEFAULT '0.0.0.0',
  `label` varchar(24) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `siteId` (`siteId`),
  KEY `siteId_id_ndx` (`siteId`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

Update: Here are the create table statements:

mysql> SHOW CREATE TABLE readings\G
*************************** 1. row ***************************
       Table: readings
Create Table: CREATE TABLE `readings` (
  `time` int(11) NOT NULL,
  `boxsn` varchar(16) NOT NULL,
  `rev` varchar(16) NOT NULL,
  `schema` tinyint(3) unsigned NOT NULL,
  `interval` smallint(5) unsigned NOT NULL,
  `relay` tinyint(4) NOT NULL,
  `inputV` decimal(10,6) NOT NULL,
  `inputA` decimal(10,6) NOT NULL,
  `outputV` decimal(10,6) NOT NULL,
  `outputA` decimal(10,6) NOT NULL,
  `phase` tinyint(4) NOT NULL,
  `outputVA` decimal(10,6) NOT NULL,
  `watts` decimal(10,6) NOT NULL DEFAULT '0.000000',
  `var` decimal(10,6) NOT NULL,
  `kiloVAHours` decimal(9,9) DEFAULT '0.000000000',
  `kilowattHours` decimal(9,9) NOT NULL,
  `kilovarHours` decimal(9,9) NOT NULL,
  PRIMARY KEY (`boxsn`,`time`),
  KEY `time` (`time`),
  KEY `boxsn_time_ndx` (`boxsn`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE boards\G
*************************** 1. row ***************************
       Table: boards
Create Table: CREATE TABLE `boards` (
  `id` varchar(16) NOT NULL,
  `siteId` int(11) NOT NULL,
  `groupId` int(11) DEFAULT '0',
  `lastReport` int(11) DEFAULT NULL,
  `lastIp` varchar(15) DEFAULT '0.0.0.0',
  `label` varchar(24) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `siteId` (`siteId`),
  KEY `siteId_id_ndx` (`siteId`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1
source | link

Query performance with subquery and IN clause

I am trying to select a range of data for multiple devices (unique serial numbers) from a historical table and was wondering why there is such a big difference in time for the following queries:

Basically I am trying to use the IN clause to indicate what items I want to fetch data for. If I "hard code" the items in the IN clause, the query is fast, if I use a subquery or join to select the items the performance is poor.

This query completes in 0.15s and returns 7382 rows.

SELECT `readings`.* FROM `readings`
WHERE
  (SerialNumber IN ('091146000121', *snip 25*, '091146000556'))
AND (readings.time >= 1325404800)
AND (readings.time < 1326317400)
ORDER BY `time` ASC

The same query rewritten using a subquery to get the serial numbers takes over 30 seconds, and seems to spend most of its time in the Preparing state. It returns the same data as the first query.

SELECT `readings`.* FROM `readings`
WHERE
  (SerialNumber IN (SELECT `boards`.`id` AS `SerialNumber` FROM `boards` WHERE (siteId = '1')))
AND (readings.time >= 1325404800)
AND (readings.time < 1326317400)
ORDER BY `time` ASC

The subquery returns the same values that are in the first query, but as stated, this takes a lot longer to run. Are they not functionally equivalent?

Here is the explain for both queries:

+----+-------------+----------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra                       |
+----+-------------+----------+-------+---------------+---------+---------+------    +------+-----------------------------+
|  1 | SIMPLE      | readings | range | PRIMARY,time  | PRIMARY | 22      | NULL | 7339 | Using where; Using filesort |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-----------------------------+

+----+--------------------+----------+-----------------+----------------+---------+---------+------+---------+-------------+
| id | select_type        | table    | type            | possible_keys  | key     | key_len | ref  | rows    | Extra       |
+----+--------------------+----------+-----------------+----------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY            | readings | range           | time           | time    | 4       | NULL | 6353234 | Using where |
|  2 | DEPENDENT SUBQUERY | boards   | unique_subquery | PRIMARY,siteId | PRIMARY | 18      | func |       1 | Using where |
+----+--------------------+----------+-----------------+----------------+---------+---------+------+---------+-------------+

For some reason the query with the subselect isn't using the primary key. I tried using USE INDEX, but that actually made it take a lot longer.

The readings table has PRIMARY KEY SerialNumber, time with an index on time.
The boards table has PRIMARY KEY id (SerialNumber) and index on siteId.

The MySQL version I'm using is 5.5.8-log MySQL Community Server (GPL)

I'm just wondering why the performance of both queries isn't very similar. Thanks.