SQL Queries - Where to find more optimization tidbits like a=b AND b=c slower than a=b AND b=c AND a=c?

Question

I was just wondering where I could find more information on these optimizations? Google searches tend to emphasize prepared queries and such, and not really at the angle of the abstraction the SQL provides.

Source: http://www.joelonsoftware.com/articles/LeakyAbstractions.html

The SQL language is meant to abstract away the procedural steps that are needed to query a database, instead allowing you to define merely what you want and let the database figure out the procedural steps to query it. But in some cases, certain SQL queries are thousands of times slower than other logically equivalent queries. A famous example of this is that some SQL servers are dramatically faster if you specify "where a=b and b=c and a=c" than if you only specify "where a=b and b=c" even though the result set is the same. You're not supposed to have to care about the procedure, only the specification. But sometimes the abstraction leaks and causes horrible performance and you have to break out the query plan analyzer and study what it did wrong, and figure out how to make your query run faster.

Looking at MySql in particular.

Answer 1

You can try SQL Server Performance, although I think it's geared towards MS SQL Server more than other RDBMSs. Personally, I look at performance tuning as a process more than a collection of tidbits.

Once you get the process down you're likely to come across single item optimizations as you go, but it's the process itself that will give you the most bang for your buck. Learn how to read query plans (or the equivalent in your RDBMS), learn the insides/behind the scenes implementation of your server, how it stores and uses indexes, how to find bottlenecks in IO, memory, locking, etc.

Answer 2

Books are better than web searches to learn performance tuning for a database. It is a complex subject and varies greatly from datbase to database and even as @OMGPonies said from version to version.

Only My SQL Performance book I found at amazon, don;t know how good it is: http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/0596101716/ref=sr_1_1?ie=UTF8&s=books&qid=1277756707&sr=8-1

Answer 3

"these" are not optimizations.
Learn profiling - the source of all optimizations.
That's all you need.

One you mentioned is not "optimization tidbit". It was an example of totally different subject.
And it is not supposed for blind usage.
But only as a result of profiling, if applicable.

The whole your approach is wrong. There are no "optimization tidbits". There are only profiling. Once you find your "where a=b and b=c" query runs slow - you can start looking for the solution, not sooner.

So, you have 2 instruments to use:

BENCHMARK your query goes here

and

EXPLAIN your query goes here

study their output and then ask particular questions, regarding your server, your settings, your database. That's the only way. No "canned recipe" could help.

As for just a curious reading, you can follow blog, named surprisingly http://mysqlperformanceblog.com