Learn SQL Database Programming

Understanding query optimization

To make sure your queries are optimized and run as quickly as they can, you want to ensure that they are correctly using the indexes on the table you are querying. If you use indexes properly with your queries, then you will have less blocking and deadlocking to deal with because the index will allow you to query data more quickly.

There is a simple way to see how MySQL will run your query, and that is to append EXPLAIN to the front of your query. For example, you can see EXPLAIN being used in the following query:

USE lahmansbaseballdb;EXPLAIN SELECT playerid, g_all, g_batting, g_defense FROM appearances;

What EXPLAIN will do is give you a table of information about how it's going to run the query. The ...