Measuring SQL Server Performance

Example #2 – avoid OR conditions

Consider this query, which selects the amount of posts on Facebook posted after new year’s eve, or posted by a user named Mark.

SELECT count(*) FROM fb_posts WHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

Having an index on both the username and post_time columns might sound helpful, but in most cases, the database won’t use it, at least not in full. The reason will be the connection between the two conditions – the OR operator, which makes the database fetch the results of each part of the condition separately.

An alternative way to look at this query can be to ‘split’ the OR condition and ‘combine’ it using a UNION clause. This alternative will allow you to index each of the conditions separately, so the database will use the indexes to search for the results and then combine the results with the UNION clause.

WHERE username = ‘Mark’
WHERE post_time > ‘2018-01-01’

Please note that if you don’t mind duplicate records in your result set, you can also use UNION ALL (which will perform better than the default UNION DISTINCT).

Example #3 – Avoid sorting with a mixed order

Consider this query, which selects all posts from Facebook and sorts them by the username in an ascending order, and then by the post date in a descending order.

SELECT username, post_type FROM fb_posts ORDER BY username ASC, post_type DESC

MySQL (and so many other relational databases), cannot use indexes when sorting with a mixed order (both ASC and DESC in the same ORDER BY clause). This changed with the release of the reversed indexes functionality and MySQL 8.x.

So what can you do if you didn’t upgrade to the latest MySQL version just yet? First, we’d recommend to re-consider the mixed order sort. Do you really need it? If not, avoid it.

So you decided you need it, or your product manager said: “No way we can manage without it”? Another option will be to use Generated columns (available on MySQL 5.7.5+) to create a reversed column and sort on that column instead of the original. As an example, assume you’re sorting on a numeric column, you can create a generated column with the negative numeric value that correlates to the original number and sort on that new column in the opposite order. That way, all columns will have the same sort order in the ORDER BY clause, but the sort will happen as originally defined by your product’s requirement.

The last potential solution won’t always be an option, so your last resort will be upgrading to the latest MySQL version which supports mixed order sorting using indexes

0 Comment

Leave a Reply