MySQL slow_log table: querying takes forever

I'm running a query on the slow_log:

SELECT start_time FROM mysql.slow_log WHERE start_time < TIMESTAMP '2016-01-31 00:00:00'

Without a WHERE clause (using a LIMIT), the selects are fast.

However the LIMIT gives me the oldest logs, ORDER BY has the same problem for me.

Is this a MySQL thing, or does it have to do with the fact I'm running a Google Cloud SQL database?

Answer originally left as a comment by Michael - sqlbot:

Mostly it's a MySQL thing. The slow query log "table" -- by default -- is actually just a CSV file read by the "CSV storage engine," which emulates a table but uses a simple CSV file as its tablespace.

This allows appending to the slow query "table" to be a low overhead operation, but of course a CSV file has no indexes, so it has to be scanned from the beginning.

I haven't used Google Cloud SQL so I can't offer an authoritative answer on what options you might have, there, such as changing it to a MyISAM table and indexing it.

See Selecting General Query and Slow Query Log Output Destinations in the product documentation.

StackExchange Database Administrators Q#150948, answer score: 4