Search code examples
mysqlsqldatabaseperformanceinnodb

Slow SQL Queries after listing larger table

Lets call the database in question db and lets also call the smaller table with a few hundred rows x and the larger with above 10k rows y.

When I issue a query to table x, the query is issued without any problems in about 0.0003 seconds using the basic query SELECT * FROM x LIMIT 25.

enter image description here

This applies to all smaller tables in db with all queries taking about 0,3ms to process.

However, after issuing the same basic query SELECT * FROM y LIMIT 25 to table y the process is significantly slower. Sometimes taking up 100ms to process.

Snip

It effects the query time for table y and the query time for table x.

Snip

The problem seems to go away after either re-issuing the listing of table x a few times or listing another small table in db. A table from another database will still be slow.

I've read a lot about optimization and performance enhancements that can be made to increase query speeds. None of them seem to cover this issue however.

Solution

  • Optimization and Performance and table size are not the issues; caching is.

    • Since there is no WHERE, GROUP BY, nor ORDER BY, indexing does not matter.

    • LIMIT 25, without ORDER BY, will grab the first 25 rows; it does not matter how many rows there are after those.

    • If the "Query cache" in on, then the first time you run a query, it will be slower. After that (until bumped out of cache), it will be "fast" -- 0.3ms is pretty typical.

    • Even if the QC is off, there are other caches involved that could explain the difference.