SELECT count(*) is twice slower than SELECT count(*), some_column in MySQL

Question

I have two simple queries:

1. SELECT count(*) FROM table1 WHERE cond1=exp1 AND cond2=exp2

   Returns number of rows. e.g. 47.

2. SELECT count(*),some_column FROM table1 WHERE cond1=exp1 AND cond2=exp2

   Returns number of rows, e.g. 47, and an arbitrary value from some_column, and it's twice as fast as the first query! I tested it directly on the server, of course over GUI (HeidiSQL), the results are the same, query 2 is twice as fast!

Any idea why this is the case?

I'm using MySQL server 5.6.21 and Apache/PHP 5.3 on Win Server 2012.

UPDATE 1:

CREATE TABLE `programs` (
    `tvp_id` INT(11) NOT NULL AUTO_INCREMENT,
    `tvp_time` TIME NOT NULL DEFAULT '00:00:00',
    `tvp_time_end` TIME NOT NULL DEFAULT '00:00:00',
    `tvp_date` DATE NOT NULL DEFAULT '0000-00-00',
    `tvp_title` VARCHAR(200) NOT NULL,
    `tvp_channel` INT(11) NOT NULL DEFAULT '0',
    `tvp_type` VARCHAR(20) NOT NULL,
    `tvp_description` TEXT NOT NULL',

    ... more and more columns ...

    PRIMARY KEY (`tvp_id`),
    INDEX `tvp_date` (`tvp_date`),
    INDEX `tvp_channel` (`tvp_channel`),
    INDEX `tvp_time` (`tvp_time`),
)
ENGINE=MyISAM

I clear the caches and run queries multiple times - the result are the same - 2) query is 2 times faster.

Specific queries for my table are:

1.  SELECT COUNT(*)            FROM programs WHERE (tvp_chanel = value_channel) AND (tvp_date = value_date)

or

2.  SELECT COUNT(*), tvp_type FROM programs WHERE (tvp_channel = value_channel) AND (tvp_date = value_date)

so both columns in WHERE clause are columns with INDEX.

I try EXPLAIN for this queries:

1. "id" "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "programs"  "index_merge"   "tvp_date,tvp_channel"  "tvp_channel,tvp_date"  "4,3"   \N  "15"    "Using intersect(tvp_channel,tvp_date); Using where; Using index"

---

2. "id" "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "programms" "index_merge"   "tvp_date,tvp_channel"  "tvp_channel,tvp_date"  "4,3"   \N  "15"    "Using intersect(tvp_channel,tvp_date); Using where"

The different is "Using index" in 1) query. So why is 1) query slower?

Answer 1

This is perhaps a shot in the dark, but I can imagine that the optimizer may be unwisely choosing to full scan a nonclustered index (perhaps the primary key), whereas the the second somehow prods the optimizer to full scan the clustered index (AKA no index at all, just looking at the rows as they are stored).

Or maybe not. Who can tell the ways of the optimizer in any DBMS?

Answer 2

I noticed that your table engine is myisam, is there a reason that you have to use it? Anyway, is it possible for you to convert it to innodb and see what happens with these two queries?

BTW, and this might be irrelevant to your question, index merge is not always good, it might even be a performance killer in some cases. See this for an example: http://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/

The point is, a lot of single column index might be a dangerous sign. For example, if your query is

SELECT COUNT(*) FROM programs WHERE (tvp_chanel = value_channel) AND (tvp_date = value_date),

an index on (tvp_chanel, tvp_date) is a covering index, which is the most ideal case. (Yeah I know this might be irrelevant after all, just can't help mentioning it)

And just in case, use select sql_no_cache to disable query cache when testing

Answer 3

Not a direct answer to your question, but why not use count(1) instead of count(*)? As Patrick suggested, it could be a problem with the optimizer choosing which column to count on. If you use count(1) there is no column involved, just a count of the number of records returned.