3 ms·

I know the article is about a MS stack, but this is not true for MySQL. It's text based so even an extra space causes a miss on the query cache.

raganwald 17y ago
What happens in MySQL when the queries are character-for character identical except for literals, e.g WHERE t.foo = 'bar' vs. WHERE t.foo = 'blitz'? update: NVM! http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cac...
- andrewcooke 17y ago
  From the conclusions: Only identical queries may be serviced from the cache. This includes spacing, text case, etc. Which is disappointing. I will go look at Postgres... and it seems to cache only at a lower level (indices, tables etc).
  - Periodic 17y ago
    If you cache on the level of SQL-statement strings you can skip the transformation into the AST. It's very cheap to quickly hash the string and look it up. If you have a site that is going to use the same queries over and over (e.g. for front-page data or popular categories) then the query will likely be exactly the same and the string hash is cheaper. If you had to build an AST for every single query you're just adding an extra layer of complexity that won't gain you much. The only reason I can think of for a query with different literals in the conditionals to have a different plan would be if a histogram of frequencies was actually kept such that different literals created large differences in result sizes which then get fed into a join and affect the join logic. But that's going to be pretty rare and is a level of optimization that is unnecessary. It's also not going to make a huge difference unless the query is very complex, because the plans for simple queries are... simple.
    - andrewcooke 17y ago
      either i've misunderstood, or you're missing the point. if you cache the statement then you get a miss if the different literals change. remember that (again, unless i have misunderstood) we are not talking about compiled statements, but about linq generate the "complete text". i agree that the same plan would normally be used; the problem is hitting the cache to find that plan if the value of a variable changes.

raganwald 17y ago

What happens in MySQL when the queries are character-for character identical except for literals, e.g WHERE t.foo = 'bar' vs. WHERE t.foo = 'blitz'? update: NVM! http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html http://dev.mysql.com/tech-resources/articles/mysql-query-cac...

andrewcooke 17y ago
From the conclusions: Only identical queries may be serviced from the cache. This includes spacing, text case, etc. Which is disappointing. I will go look at Postgres... and it seems to cache only at a lower level (indices, tables etc).
- Periodic 17y ago
  If you cache on the level of SQL-statement strings you can skip the transformation into the AST. It's very cheap to quickly hash the string and look it up. If you have a site that is going to use the same queries over and over (e.g. for front-page data or popular categories) then the query will likely be exactly the same and the string hash is cheaper. If you had to build an AST for every single query you're just adding an extra layer of complexity that won't gain you much. The only reason I can think of for a query with different literals in the conditionals to have a different plan would be if a histogram of frequencies was actually kept such that different literals created large differences in result sizes which then get fed into a join and affect the join logic. But that's going to be pretty rare and is a level of optimization that is unnecessary. It's also not going to make a huge difference unless the query is very complex, because the plans for simple queries are... simple.
  - andrewcooke 17y ago
    either i've misunderstood, or you're missing the point. if you cache the statement then you get a miss if the different literals change. remember that (again, unless i have misunderstood) we are not talking about compiled statements, but about linq generate the "complete text". i agree that the same plan would normally be used; the problem is hitting the cache to find that plan if the value of a variable changes.