Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any technique/advice in order to optimize the performance of the queries below?

According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, will make huge difference.

My DB engine is innoDB and I'm using 1 core CPU, with 2GB RAM.

Exec Time : 7,5 sec (5,5 mil. rows)

SELECT vpn_group, username, from_interface_addr_ip
FROM SystemEventsR
WHERE (timestamp > ( NOW( ) - INTERVAL 10 MINUTE ) AND 
SysLogTagflag=1 AND 
username !='')
GROUP BY username

Exec Time : 88,4 sec (5,7 mil. rows)

SELECT syslogtagid, DeviceReportedTime, class, definition, SystemEventsR.SysLogTag,
COUNT(id) AS Records, 
explanation, action 
FROM SystemEventsR,oa_syslogtags
WHERE  (SystemEventsR.SysLogTag = oa_syslogtags.syslogtag AND 
flagid = 1 AND DATE(timestamp) = DATE(NOW()))
GROUP BY SystemEventsR.SysLogTag 
ORDER BY Records DESC

Any help would be appreciated.

share|improve this question
4  
I think the long and short of it is going to be "have the right indexes" -- ensure your tables are indexed on everything you group, order or search based on. –  James Green Jun 19 '13 at 21:29
4  
30M rows/day? Are you sure? This will give you 1 billion rows in a single month. You'll need much more hardware resources for sure, even with the right indexes. –  setsuna Jun 20 '13 at 18:42
add comment

migrated from stackoverflow.com Jun 24 '13 at 13:08

This question came from our site for professional and enthusiast programmers.

2 Answers

There are useful tips in the mysql online documentation:

http://dev.mysql.com/doc/refman/5.7/en/optimization.html

It's worth reading about indexing, like James Green has suggested.

Also you may wish to use the keyword "EXPLAIN" to look at the query plan to find potential bottlenecks. This will highlight which indexes are being used in your query. To make sure you're query plan is correct you will need to keep updating index statistics on your database.

Hope this helps and let me know if you find anything!

Many Thanks,

Simon.

share|improve this answer
    
Good call on EXPLAIN, I always forget that –  James Green Jun 19 '13 at 22:45
add comment

Your queries are only on the latest data. You may try horizontal partitioning. It is supported in MySQL 5.1.

Another way is to maintain a "horizontal partitioning" manually. For example, you may create a table "SystemEventsR_archive", and run some queries as below every day.

REPLACE INTO SystemEventsR_archive SELECT * FROM SystemEventsR;
DELETE FROM SystemEventsR WHERE timestamp < ( NOW() - INTERVAL 1 DAY );
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.