MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query Optimisation of a slow query
Posted by: Rick James
Date: September 20, 2011 09:20PM

Use the DATETIME datatype instead of this:
`date_time` varchar(23) NOT NULL default '',

I suspect this is giving the wrong answer:
date_time < a.date_time + interval 1 second

The second of these is redundant and can be DROPped:
UNIQUE KEY `traffic_id_date_time`( `network_traffic_id` , `date_time` ),
KEY `network_traffic_id`( `network_traffic_id` ),

innodb_buffer_pool_size is 2G, I assume all your tables are InnoDB. If so, then caching is no the issue. With 20GB of RAM, the buffer pool could be made much larger (but this may not help the current issue).

* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes

It's a "correlated subquery" (see a.network_traffic_id), so it cannot be turned into a temp table without some further thought.

Why
date_time < a.date_time + interval 1 second
instead of
date_time <= a.date_time
?

With that change, there is hope for the 'group by trick':
http://forums.mysql.com/read.php?10,428347 (Last or Max?)
http://forums.mysql.com/read.php?10,400775 (Assistance required for query with subquery)
http://forums.mysql.com/read.php?10,396680 (Slow Query)
http://forums.mysql.com/read.php?10,394154 (Require help to formulate a query.)
http://forums.mysql.com/read.php?20,389565 (query returns unwanted duplicates)
http://forums.mysql.com/read.php?10,387554 (aggreate data)
http://forums.mysql.com/read.php?10,373984 (select max date in Inner Join)
http://forums.mysql.com/read.php?20,291994 (create function help)
http://forums.mysql.com/read.php?10,290067 (Help with minimum/group by)
http://forums.mysql.com/read.php?10,284734 (max function)
http://forums.mysql.com/read.php?10,280124 (limiting rows returned)
http://forums.mysql.com/read.php?52,272271 (Subquery and JOIN's)
http://forums.mysql.com/read.php?10,266585 (How does group by algorithm works?)
http://forums.mysql.com/read.php?10,250033 (Listing threads by newest posts?)
http://forums.mysql.com/read.php?10,236805 (Complex Select with Min/Max Syntax)

Options: ReplyQuote


Subject
Views
Written By
Posted
3364
September 13, 2011 03:26AM
1197
September 16, 2011 10:21PM
1120
September 19, 2011 01:01AM
Re: Query Optimisation of a slow query
1020
September 20, 2011 09:20PM
928
September 21, 2011 12:21AM
986
September 21, 2011 02:38AM
957
September 22, 2011 12:03AM
964
September 20, 2011 01:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.