StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to make this complicated query faster [MySQL]?
text
Body
copied!<p>I have the next query:</p> <pre><code>SELECT JL.j_id, COUNT(*) as total FROM j_log JL WHERE JL.log_time > '20120205164008' AND JL.j_id IN ( SELECT j_id FROM j WHERE checked = '1' AND expires >= '20120207164008' ) GROUP BY JL.j_id ORDER BY total DESC LIMIT 3 </code></pre> <p>j table has big structure 100 fields and 248986 rows inside it.</p> <p>next KEY's are present in it</p> <pre><code> PRIMARY KEY (`j_id`), KEY `expires` (`expires`), KEY `checked` (`checked`), KEY `checked_2` (`checked`,`expires`) </code></pre> <p>j_log table has about 63000000 records and the next structure</p> <pre><code>CREATE TABLE `j_log` ( `j_id` int(11) NOT NULL DEFAULT '0', `member_id` int(11) DEFAULT NULL, `ip` int(10) unsigned NOT NULL DEFAULT '0', `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY `j_id` (`j_id`), KEY `log_time` (`log_time`), KEY `ip` (`ip`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | </code></pre> <p>so the considered query wants to get top3 of most visited j_id instances </p> <p>this is the plan</p> <pre><code>+----+--------------------+-------+-----------------+-----------------------------------+---------+---------+------+----------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+-----------------+-----------------------------------+---------+---------+------+----------+----------+----------------------------------------------+ | 1 | PRIMARY | JL | index | log_time | j_id | 4 | NULL | 63914602 | 0.36 | Using where; Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | j | unique_subquery | PRIMARY,expires,checked,checked_2 | PRIMARY | 4 | func | 1 | 100.00 | Using where | +----+--------------------+-------+-----------------+-----------------------------------+---------+---------+------+----------+----------+----------------------------------------------+ </code></pre> <p>Some times it could take up for 15!!! minutes.</p> <p>Is there any way how to make faster ?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload