Mysql query is faster

Question

Table structure:

CREATE TABLE IF NOT EXISTS `logs` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user` bigint(20) unsigned NOT NULL, `type` tinyint(1) unsigned NOT NULL, `date` int(11) unsigned NOT NULL, `plus` decimal(10,2) unsigned NOT NULL, `minus` decimal(10,2) unsigned NOT NULL, `tax` decimal(10,2) unsigned NOT NULL, `item` bigint(20) unsigned NOT NULL, `info` char(10) NOT NULL, PRIMARY KEY (`id`), KEY `item` (`item`), KEY `user` (`user`), KEY `type` (`type`), KEY `date` (`date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0 ROW_FORMAT=FIXED; 

Query:

 SELECT logs.item, COUNT(logs.item) AS total FROM logs WHERE logs.type = 4 GROUP BY logs.item; 

The table stores 110 thousand records, of which 50 thousand records are of type 4. Execution time: 0.13 seconds

I know this is fast, but can I do it faster?

I expect 1 million records, and thus, time will grow quite a bit.

Answer 1

Query analysis with EXPLAIN:

 mysql> EXPLAIN SELECT logs.item, COUNT(logs.item) AS total FROM logs WHERE logs.type = 4 GROUP BY logs.item\G id: 1 select_type: SIMPLE table: logs type: ref possible_keys: type key: type key_len: 1 ref: const rows: 1 Extra: Using where; Using temporary; Using filesort 

"Using temporary using file management" indicates some expensive operations. Since the optimizer knows that he cannot rely on rows to preserve each item value, he must scan the entire table and collect the bill for a single item in the temporary table. Then sort the resulting temp table to get the result.

You need an index in the log table in the columns (type, element) in that order. The optimizer then knows that it can use the index tree to scan each logs.item value completely before moving on to the next value. By doing this, he can skip the temporary table to collect the values ​​and skip the implicit sort of the result.

 mysql> CREATE INDEX logs_type_item ON logs (type,item); mysql> EXPLAIN SELECT logs.item, COUNT(logs.item) AS total FROM logs WHERE logs.type = 4 GROUP BY logs.item\G id: 1 select_type: SIMPLE table: logs type: ref possible_keys: type,logs_type_item key: logs_type_item key_len: 1 ref: const rows: 1 Extra: Using where