Geek Answers Handbook

SELECT (*) counter is twice as slow as SELECT (*) counter, some_column in MySQL

I have two simple queries:

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

    Returns the number of rows. for example 47.

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

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

Any idea why this is so?

I am 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 requests several times - the result is the same - 2) the request is 2 times faster.

Specific queries for my table:

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)

therefore, both columns in the WHERE clause are columns with INDEX.

I am trying to execute EXPLAIN for these 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"

" " 1 ". , 1) ?

+4
3

, count (1) count (*)? , , . count (1), , .

+1

, , , , (, ), - (AKA , , ).

, , . ?

0

, myisam, , ? , innodb , ?

, , index merge , . . : http://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/

, . ,

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

(tvp_chanel, tvp_date) , . (, , , )

, select sql_no_cache,

0

More articles:


All Articles