4

I am trying to profile two different queries that do the same thing to find which one is faster. For testing, I have put SQL_NO_CACHE into both queries to prevent the query cache from messing up the timing.

Query A is consistently 50ms. Query B is 100ms the first time it is run and 10ms if I run it a second time shortly after.

Why is Query B faster the second time? The query cache should not be speeding up the queries. Could it be that the first run of query B loads the data from disk into memory so that the second query is running in memory and faster? Is there a way to test this? I tried to test this myself by doing select * from the table before I ran Query B, but it still exhibited the same behavior. Is SQL_NO_CACHE perhaps not working to disable the query cache?

Query B looks something like this: SELECT SQL_NO_CACHE foo,bar FROM table1 LEFT JOIN table2 ON table1.foo=table2.foo WHERE bar=1

Jake
  • 1,135
  • 1
  • 12
  • 26

3 Answers3

2

Depending on the storage engine you're using, yes it is most probably being loaded from a data cache and not a query cache.

MyISAM provides no storage engine level caching for data, and only caches indexes. However, the operating system often serves up data from its own caches which may well be speeding up your query execution.

You can try benchmarking the query in a real scenario, just log that specific query to the database every time its executed (along with its execution time).

JamesHalsall
  • 13,224
  • 4
  • 41
  • 66
  • Thanks for the tips. I should have mentioned that I am using innodb tables. Is there a way to tell what is in memory and what isn't? – Jake Feb 10 '11 at 20:50
  • Not that I know of, it becomes impossible to monitor accurately because its the operating system's caching. If you're using InnoDB then it will be InnoDB's buffer pool that is caching the data, I'm not sure that this is an option that you can turn off. But it's doing its job. – JamesHalsall Feb 10 '11 at 21:03
1

Depending on the size of your indexes and your table type, it may be that indexes are not in memory the first time the query is run. So MySQL will pull indexes into memory the first time the query is run, causing a significant slowdown. The next time, most of what MySQL needs may in memory, resulting in the performance gain.

Chris Henry
  • 11,914
  • 3
  • 30
  • 31
0

Is your app making a connection and doing the authentication handshake on the first query? If so the 2nd query will already have an open/authenticated connection to execute from. Try running it a 3rd time and see if the 2nd and 3rd tries are close to the same time.

Daniel Knoodle
  • 384
  • 1
  • 4
  • I am doing the queries in the terminal so there should be no connection startup costs. The 3rd and up runs are all fast. Only the first query is slow. – Jake Feb 10 '11 at 20:51
  • It wouldn't surprise me if even the terminal had to make a connection on the first execution. What if you open multiple terminals? Do you get the same results? – Daniel Knoodle Feb 10 '11 at 21:45