How can I learn to optimize SQL queries [closed]

Question

I know how to write SQL queries and can get the results I need. But sometimes my queries are slow and I don't fully understand why.

What are some good resources for learning to write efficient queries and to optimize the queries I've already written?

Answer 1

I would say the main things are:

• Understand the set-based nature of SQL by reading platform-neutral books such as Celko's; this will help you avoid making newbie mistakes, like using cursors (or other iterative approaches) where they are not needed.
• Learn the basics of normalization and when to denormalize; efficient queries start with well-organized data.
• Understand where indexes can be helpful and where they can't; e.g., grasp how the cardinality of your data affects index efficiency, what SARGable queries are, and when to use multi-column indexes.
• Learn how to use EXPLAIN PLAN or its equivalent for your platform; by observing how your query is being compiled and the resources it is consuming, you will better understand the bottlenecks.
• Learn platform-specific methods such as indexed/materialized views, full text indexing, and methods for paging and dealing with hierarchical data.

Answer 2

For MySQL specific information, chapter 7 of the reference manual is all about optimization. § 7.3, in particular, covers MySQL statement optimization. The PostgreSQL wiki similarly documents optimizing specifically for that RDBMS. Microsoft's "How To: Optimize SQL Queries" is retired; I'm not certain if there's a more recent document. Oracle has a couple documents on query optimization for the various versions.