MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query Optimization on time-based data
Posted by: Rick James
Date: August 05, 2010 10:35PM

1. To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes (much better than DESCRIBE)
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

2. Read about Summary tables for significantly speeding up reports of time-based data...
http://forums.mysql.com/read.php?24,372008 (Index performance)
http://forums.mysql.com/read.php?10,364300 (Thinking about sorting articles..)
http://forums.mysql.com/read.php?52,359476 (Slow 'sending data' phase on mySQL query, for simple query)
http://forums.mysql.com/read.php?24,355548 (views, unions and different dbs)
http://forums.mysql.com/read.php?106,288561 (Partitioning with range(to_days) issue)
http://forums.mysql.com/read.php?10,298557 (UNIX_TIMESTAMP Duplicate key)
http://forums.mysql.com/read.php?20,294180 (Multiple complex queries)
http://forums.mysql.com/read.php?125,287739 (Large table designissue)
http://forums.mysql.com/read.php?10,282768 (Which storage engine?)
http://forums.mysql.com/read.php?10,276359 (should i go with myisam or innodb?)
http://forums.mysql.com/read.php?125,266570 (Best way to store/access large amounts of data?)
http://forums.mysql.com/read.php?24,263259 (Slow JOIN to convert IP to Country Code)
http://forums.mysql.com/read.php?10,263071 (Bitmap Index)
http://forums.mysql.com/read.php?24,253221 (InnoDB CPU Spikes)
http://forums.mysql.com/read.php?10,254332 (Very long query doubt... (Monster query...))
http://forums.mysql.com/read.php?125,252723 (Database Design for huge set of data)
http://forums.mysql.com/read.php?10,252593 (Counters)
http://forums.mysql.com/read.php?21,247124 (Solving table locking issues)
http://forums.mysql.com/read.php?10,247779 (compressing tables)
http://forums.mysql.com/read.php?125,245133 (Design approach for summary table by 3 items)

3. Don't use LIKE when = will do:
AND `method` LIKE 'update'
AND `method` = 'update'

4. Best index for this
WHERE `data_point_source_id` =1
AND `method` = 'update'
ORDER BY `populated` DESC
is either of these:
INDEX(data_point_source_id, method, populated)
INDEX(method, data_point_source_id, populated)

Options: ReplyQuote


Subject
Views
Written By
Posted
3571
August 03, 2010 10:06AM
Re: Query Optimization on time-based data
2309
August 05, 2010 10:35PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.