How to Optimize SQL Queries

Avoid conditions with different column types
Consider this query, which selects the number of red fruits in a forest.

SELECT count() FROM forest WHERE fruit_color = 5; / 5 = red */

Assuming the column fruit_color‘s type is VARCHAR, or just anything non-numeric, indexing that column won’t be very helpful, as the required implicit cast will prevent the database from using the index for the filtering process.

So how can you tune this SQL query? You have two options to optimize this query. The first one would be to compare the column to a constant value that matches the column’s type, so if it’s a VARCHAR column, compare it to ‘5’ (with single quotes) and not to 5 (which is a numeric comparison which will result in an implicit cast).

A better option will be to adjust the column’s type to match the most suitable type for the values the column holds. In this example, the column should be altered to an INT type. Please note that altering a column’s type can be a complicated task, so read about the challenges of that task before heading towards it.

Avoid LIKE searches with prefix wildcards
Consider this query, which searches all Facebook posts from a username which includes the string ‘Mar’, so we are searching for all posts written by users named Mark, Marcus, Almar, etc.

SELECT * FROM fb_posts WHERE username like ‘%Mar%’

Having a wildcard ‘%’ at the beginning of the pattern will prevent the database from using an index for this column’s search. Such searches can take a while..

In this case, there are two options to improve this query’s performance. The first one is trivial – consider whether the prefix wildcard is important enough. If you can manage without it, get rid of it.

Another option will be to use full-text indexes. Please note though, that these indexes and the MATCH … AGAINST syntax aren’t free from challenges and have some differences when compared to the familiar LIKE expressions in MySQL.

0 Comment

Leave a Reply