I'm using a Google Cloud SQL instance for a project on Google App Engine. I have enabled the slow query log flag to store slow queries. But the slow_log table's performance is really bad, just for selecting some data took about 6 minutes.
I have also noted that the logged queries are not really "slow", i mean they have 0 as query time and lock time. See example:
mysql> SELECT * FROM slow_log LIMIT 900000,1; start_time: 2015-05-01 20:06:16 query_time: 00:00:00 lock_time: 00:00:00 rows_sent: 1 rows_examined: 1 sql_text: select * fromserviceswherename= 'facebook' limit 1 1 row in set (6 min 23.37 sec)
Should I add some indexes to the table? or should I truncate it to decrease the number of rows?
I have found the problem, the flag long_query_time was set to 0, so every log was stored.
A slow query is not one that takes a long time to execute. That wouldnt be useful as a detection tool since you can detect that yourself with simple timing.
What it is telling you is badly designed queries Which when compiled it detects there are no good indexes and will have to scan the whole table.
. this is crucial because its telling you the query will be a problem with large data even if now takes zero time.
Tldr: add the appropiate indexes.