4

Possible Duplicate:
Sql wildcard: performance overhead?

Studying my applications preformance, I realized that the query:

SELECT * FROM (`static_cache`) WHERE `combined_name` =  'base'

Takes about 0.0052s to run. Sure, it might not be a lot, but a few of these querys quickly add up to a bit of loading time. Experimenting, I tried to select only the fields that I actually needed in the query instead (logical optimization), yielding the query:

SELECT `combined_name`, `timestamp`, `type`, `checksum` FROM (`static_cache`) 
       WHERE `combined_name` =  'base'

This query, however, takes as little as 0.0005s to run. That's 10 times faster, and the only field and I am not selecting is the id-column. A speed increase was expected, but not by 10 times. Yes, I tested it repetitively.

How can the change in this query possibly decrease the running time by so much?

Community
  • 1
  • 1
Zar
  • 6,786
  • 8
  • 54
  • 76
  • I actually tested to select ALL the fields, increasing the running time to 0.0012s, which still almost makes the query 5 times faster. – Zar Jul 14 '12 at 17:11
  • 1
    The overhead is from compiling the query, not from running it. If you are using this query many times, then use a prepared statement. – Gordon Linoff Jul 14 '12 at 17:23
  • 1
    @juergend I do not believe that my question is a duplicate, especially since my findings differ from the accepted answer. – Zar Jul 14 '12 at 17:34
  • @GordonLinoff Is the planner really that slow in looking up the table column names? (even on a very quick query) :( –  Jul 14 '12 at 18:03
  • @Zar Consider updating the question to reflect your first comment/title. –  Jul 14 '12 at 18:04
  • 1
    @pst . . . presumably. You can do the timings on your system. The point is that the execution of the two queries is the same, unless the excluded column is really, really big. A few milliseconds to compile and optimize a query is not particularly slow. There's a reason why database vendors include prepared and pre-compiled queries. – Gordon Linoff Jul 14 '12 at 18:05
  • @GordonLinoff Can't argue with that .. although perhaps the above posted numbers are also off, if caching is getting the way? –  Jul 14 '12 at 18:05
  • @pst Caching is turned off in the PHP-environent, is there anywhere else I need to disable caching to get accurate results? – Zar Jul 14 '12 at 19:21

0 Answers0