1

I'm trying multiple approaches at writing the same query but the query cache is interfering with my efforts.

I do a query once and it takes 3.5 seconds or so. I do it again and it takes 0.015. I change the JOINs around a bit and it still takes 0.015 seconds, making it very hard for me to see which one is faster.

I tried the following to no avail:

RESET QUERY CACHE;
FLUSH TABLES;

ie. after doing those my query still takes 0.015 seconds.

Any ideas?

1 Answers1

0

The first step in query optimization is checking execution plan. In the most cases the cheaper plan, the better performance. Then you may want to check system views (that depends on RDMS) to verify concurrency issues, number of physical/logical reads/writes, etc (it usually makes sense after some load tests).

The difference in execution time you have is because of caching - if you run the same query consequently with very short interval ,second time query is not really executed.

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • 2
    I'm not asking how to optimize queries. I'm asking how to eliminate the caching for the reasons you described. I've updated my subject line to better reflect this. –  Dec 08 '12 at 16:06
  • @ansur : Sorry, I misunderstood your question. You can try `SELECT SQL_NO_CACHE ` to see if makes a difference, but http://stackoverflow.com/questions/6666631/sql-no-cache-does-not-work says it may not work as expected and has a good explanation why. I still think using `EXPLAIN` and comparing execution plans is usually enough for optimization purposes, but if you strongly against it, you may try running some dummy query between executions of the query of interest... – a1ex07 Dec 08 '12 at 18:21
  • SQL_NO_CACHE didn't make a difference. The other suggestions in that post that worked for the OP don't work for me because I'm on Windows. My engine type is InnoDB. –  Dec 08 '12 at 18:59
  • EXPLAIN doesn't work because, among other things, one of the queries I'm trying to optimize is actually multiple queries. I'm trying to replace multiple queries with one query. ie. there's an initial SELECT, a second SELECT wherein the output of the first is put into an IN and then, after some PHP code, a third. I'm trying to replace all of that with a single SELECT. –  Dec 08 '12 at 19:03
  • Also, EXPLAIN, when I've used it, is pretty much always returning the same thing. It's like... I'm doing a LEFT JOIN on a big table with a WHERE column_name IS NULL. I found that it's faster, though, to combine the LEFT JOIN with a JOIN to reduce the number of rows the LEFT JOIN has to be applied to. But then there's the columns I use in the ON. One of them is indexed but some of them aren't. I can get less rows if I reference a non-indexed column but I'd be referencing a non-indexed column too. The EXPLAIN for most of these variants looks the same. –  Dec 08 '12 at 19:07
  • If `EXPLAIN` looks the same I'd say it performs the same... You can see difference only if execution plan is different (assuming you don't have parameter sniffing issues)... – a1ex07 Dec 08 '12 at 19:10