Geek Answers Handbook

How can I speed up a group on a query that already uses indexes?

We have a MyISAM table with about 75 rows of milion, which has 5 columns:

id (int), 
user_id(int), 
page_id (int), 
type (enum with 6 strings)
date_created(datetime).

We have a primary index in the ID column, a unique index (user_id, page_id, date_created) and a composite index (page_id, date_created)

The problem is that the request below takes up to 90 seconds to complete

SELECT SQL_NO_CACHE user_id, count(id) nr 
FROM `table` 
WHERE `page_id`=301 
and `date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59' 
AND page_id<>user_id 
group by `user_id`

This is an explanation of this request.

+----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| id | select_type | table                      | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                        |
+----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | table                      | range | page_id       | page_id | 12      | NULL | 520024 | Using where; Using temporary; Using filesort |
+----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+

EDIT: At the suggestion of ypercube, I tried to add a new index (page_id, user_id, date_created). However, mysql does not use it by default, so I had to offer it to the query optimizer. Here is a new request and explanation:

SELECT SQL_NO_CACHE user_id, count(*) nr FROM `table` USE INDEX (usridexp) WHERE `page_id`=301 and `date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59' AND page_id<>user_id group by `user_id` ORDER BY NULL


    +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
    | id | select_type | table                      | type | possible_keys | key      | key_len | ref   | rows    | Extra                    |
    +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
    |  1 | SIMPLE      | table                      | ref  | usridexp      | usridexp | 4       | const | 3943444 | Using where; Using index |
    +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
+5
source share
3 answers

Some changes that can improve the query:

  • COUNT(id) COUNT(*). id ( ) PRIMARY KEY NOT NULL, .

  • ORDER BY NULL GROUP BY. MySQL , .

  • (page_id, date_created), , , MySQL , (page_id, user_id, date_created) ( EXPLAIN, ?)

, :

(user_id, page_id, date_created) UNIQUE, id ( , ), PRIMARY KEY id. 4 .

+6

1) , , MySQL . page_id, .

2) . , , .

, . , , . .

:

i) : ID idCacheTable, date = '2012-01-03'; lastID idCacheTable, date = '2012-02-03';

ii) , , waaaaaay.

SELECT SQL_NO_CACHE user_id, count (id) nr FROM table page_id= 301 (id >= earlyliestID id <= lastID) AND page_id < > user_id user_id;

, , , .

0

, JOIN:

SELECT SQL_NO_CACHE user_id, count(id) nr
FROM `table` t
JOIN `table` t2 ON t.`user_id`= t2.`user_id`
WHERE t.`page_id`=301
and t.`date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59'
AND t.`page_id`<>t.`user_id`
group by t.`user_id`


20 (3-4 60+). JOIN - , MySql ( MySql 5.1., user_id).

0

More articles:


All Articles