Running MySQL with InnoDB:
I have a
SELECT wide_table.* query that I want to refine to
SELECT wide_table.id since that's all the calling code needs. Testing it out I've found that the execution time with
* is faster than with
id (though the time to transfer the result over the network is faster with the "refined" version).
Why would this be the case?
The query (with names changed to protect the innocent) is:
SELECT `things`.* FROM `things` WHERE `things`.`active` = 1 AND (owner_id IS NOT NULL AND owner_id > 0) AND ((`things`.`status` IN (0 , 1) OR `things`.`status` IS NULL)) AND (date < '2015-07-11 00:00:00');
There's a compound index on
date, which is being used in both versions.
For reference, the ouput of
SHOW CREATE TABLE (with irrelevant columns omitted):
CREATE TABLE `things` ( `id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) DEFAULT '0', `date` datetime DEFAULT NULL, `owner_id` int(11) DEFAULT '0', `status` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_things_on_active_and_date` (`active`,`date`), KEY `index_things_on_date` (`date`), KEY `index_things_on_owner_id` (`owner_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1862 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
After playing around with it for a while, I've found that the comparison is affected by the limit imposed on the query. With large limits, >200, the
* version reports a faster execution time. As the limit is decreased <200, the
id version gains the upper-hand. Still not sure what to make of this...
EXPLAIN on both versions of the query yields identical output, with select_type:
SIMPLE and key: