Intro

Got slow-running SQL query? This post might be able to help. It’s intended for beginner/intermediate SQLers like myself, so we’ll cover the basics: the use of an ALIAS, the different types of JOINs, the use of the index to improve performance, and a nifty feature, the UNION - heavily crediting this post - to make queries super fast.

SELECT

In this post we’ll follow the performance of a query - from very slow, but correct, to super fast. We’ll solve issues with missing column names, then when the data looks good, we’ll focus on improving performance. Here’s the table structure:

table here

We want to support querying this data in many ways through the use of an Advanced Search UI. Users can filter on different types of records and opt to show only records that belong to their group(s) or their submitter ID. A first crack at a query would be the classic SELECT *

SELECT * FROM users, user_groups, requests, request_additional_data
JOIN users ON users.user_id = user_groups.user_id
JOIN 

In order to make these types of queries possible, we’ll need to use the LEFT JOIN, which returns all rows from the left table, and matching rows from the right table.

Notes

• SUBSTRING function, use to extract a substring from a field
  • SUBSTRING(COL_NAME, 3)
• CAST function, use to cast to a type
  • CAST(SUBSTRING(COL_NAME, 3) AS INT)
• MAX, MIN
  • SELECT MAX(rating), MIN(review_count)
• If aliasing a column or using a CASE statement, have to alias or CASE again in the WHERE clause. Remember, query order: FROM+JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. So WHERE clause is evaluated before SELECT
  • SELECT age, first, (case when last is NULL then Doe else last end) FROM age, name WHERE (case when last is NULL then Doe else last end) = Doe
  • Silly example but basically when the last column is null, replace the value with “Doe”. Check for “Doe” in the WHERE clause instead of IS NULL