Optimizing mysql queries running on 50 million rows

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.

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.

StackExchange Database Administrators Q#45155, answer score: 4