CODEWITHSUNDEEP
mysqlcachingoptimizationinnodb
Francesco Tronca
Francesco Tronca

Reputation: 59

Optimizing Queries on MYSQL (InnoDB tables)

I got a problem. I need to (for didactical purposes) show the optimization process of a query (example: 2 seconds without any index, 1 second with an index.. etc).

I have a mysql database with 12 tables and 1.000.000 records in each table.

Problem is: If I execute a query first time, it takes x seconds. The second execution of the same query, takes ALWAYS 0 seconds. I tried flushing tables, flushing query cache, setting query cache to OFF in the select, setting inno_db_buffer_pool to 0M.. nothing. After the first execution of the query, MYSQL caches somewhere (I think) the result, so the next execution takes always 0 seconds.

Upvotes: 1

Views: 583

Answers (2)

user2104560
user2104560

Reputation:

You should use SQL_NO_CACHE when you run query first time to tell MySQL not to put the result into the cache. Applying SQL_NO_CACHE only after performing some query doesn't make sense. Reset your server and perform all queries with SQL_NO_CACHE prefix.

Upvotes: 0

Thomas Ruiz
Thomas Ruiz

Reputation: 3661

If you need to optimize your queries, use SQL_NO_CACHE in your SELECT statements.

Upvotes: 1

Related Questions