6 Problem Queries That Are Massively Slowing Down Your Database

Poor database performance is a thorn in every DBA’s side. And it isn’t always easy to isolate the root cause of performance issues, which only adds to the stress. 

Tuning your SQL Server databases is a great way to resolve some of your performance issues, but it may not always be obvious where you should start the optimization process. If you rule out disk space, memory, and other network and hardware performance killers, and your SQL Server performance is still lagging, it’s time to dig into your queries.

Unoptimized queries can cause myriad performance issues for your systems. On the bright side, a few common query mistakes are responsible for the bulk of database performance degradation.

If your queries suffer from any of these six problems, get ready for some serious performance tuning.

Problem Query No. 1: Like Expressions with Leading Wildcards

Leading wildcards keep MySQL from utilizing indexes, forcing a full table scan even if you’ve indexed a field within the table. Scanning all the rows in a table significantly slows down delivery of your query results, so eliminate the leading wildcard to improve efficiency. 

Problem Query No. 2: Non-Indexed Columns Used in ‘Where’ and ‘Group By’ Clauses

Indexing columns helps return query results faster because it eliminates the need for full table scans. Indexing also helps sort records when they are returned and guarantees those records are uniquely identifiable, which is particularly useful in tables with more than 10 rows.

For a bit of perspective, consider running an “explain” statement in your query analysis before and after indexing. This will give you an idea of how many rows of scanning you just saved yourself. 

Problem Query No. 3: Like Statements That Use the ‘or’ Operator Instead of a Union Clause

Running queries using the comparison operator “or” on fields or columns in a table can be useful, but applying “or” too frequently in a “where” clause is another fast track to a full table scan.

A union clause can make the SQL query run faster, especially if different indexes are optimizing each side of the query. Essentially, the union operator takes the results of two fast, indexed queries and merges them into one.

Problem Query No. 4: Wildcard Searches

If you are stuck in a situation in which you need to search with wildcards but you don’t want to take the performance hit, try using a MySQL full-text search. Full-text searches are considerably faster than searching with wildcard characters, and you get the added benefit of more relevant results when searching huge databases. 

Problem Query No. 5: Un-Optimized Database Schema

You can only improve SQL query performance so much if you don’t also optimize your database schema. Here are a few tips for improvement:

Normalize Tables

Data redundancy hurts performance, so be sure to only represent a fact once in the database. For example, if referencing a customer in more than one table, only use “customer_name” once, then use “customer_ID” for subsequent references. 

Use Optimal Data Types

Some important points to remember when it comes to data types:

• Shorter is better when designing tables. For example, use “TINYINT” data type for the “user_id” field for a system user table with fewer than 100 users. 
• Use a “date_time” data type if a field expects a date value so you don’t have to convert the records to date format after the fact.
• MySQL works better with integer values than it does with text data types, including varchar.

Avoid Null Values

Null values in a column can hurt your query results, so you may need to assign a default value for fields where records don’t require a mandatory value.

Don’t Use Too Many Columns

Wide tables (more than 100 columns) require a lot of CPU and resources to process. Unless you absolutely have to include a wide table, it’s better to split it apart into smaller, logical tables. 

Optimize Joins

SQL statements with too many joins (and joins with too many tables) negatively affect performance. Shoot for no more than 12 joins for each query.

Problem Query No. 6: Un-Cached MySQL Queries

Websites and applications that perform a lot of select queries will benefit from caching MySQL queries. MySQL query caching speeds up performance during read operations because it caches the select query alongside the resulting data set and fetches from memory (not disk) if the query is executed more than once.

Performance tuning is essential to maintaining high availability for your SQL Server databases and ensuring your end users are happy. But unfortunately, it isn’t always immediately obvious where tuning is most needed. If you have eliminated the obvious network and hardware sources for performance problems, shift your focus to your queries.

If you’ve been working as a DBA for any length of time, you have likely encountered one (or all) of these problem queries. Now that you know what to look out for, be proactive in your performance improvement initiative and correct these common query pain points so you can reap the rewards of SQL Server query optimization.