LIKE with an initial wildcard character – SQL Smell

Leading wildcards with LIKE an SQL Smell
Leading wildcards with LIKE are an SQL Smell

Phil Factor says “using LIKE in a WHERE clause with an initial wildcard character” is an SQL Smell, but why? Why does the position of the wildcard make a difference?

This really is a problem, and it gets more serious with larger tables.

I’m going to explain what the problem is from an analyst’s point of view.

What LIKE does for us

Like in the SQL WHERE clause
Like in the SQL WHERE clause

LIKE identifies rows which match the criteria. It can be used with character columns and combined with other conditions.

The first time I encountered this statement I was amazed that it worked at all! This simply demonstrates how SQL protects us from what is really happening underneath the surface.

Like with a trailing wildcard using an index
Like with a trailing wildcard using an index

Appearances can be deceptive. LIKE only appears to match the search argument against the contents of the table column. It has to use an index in order to perform properly. The entries in the index are sorted. SQL uses the sort sequence with “trailing” wildcards, to get to the first match quickly, and then ignore any entries past the last match.

The problem with leading wildcards

Valid LIKE criteria - But two may cause problems
Valid LIKE criteria – But two may cause problems

SQL cannot use an index to matching entries when there is a leading wildcard. As a consequence it will probably scan the whole table. This will work but it may take longer than you want.

The problem gets worse for tables with more rows because with a leading wildcard LIKE has to test every row.

There will be times when you need to use a leading wildcard it in a query. Go ahead and use it, just expect poor performance. If this query which will be used repeatedly, or in a program, then discuss what you are trying to do with the people who look after your database.

Summary

Phil Factor describes leading wildcards with LIKE as an SQL Smell. This may be unavoidable in some ad-hoc queries, but you should treat it with caution if you intend to reuse the query or use it with large tables.

Where next?

The next, and final, blog post is on “Floating Point Numbers”. Do you use floating point numbers in your database? Phil Factor thinks this is an SQL Smell too. Find out why in the next article.

It’s always good to have a plan!

This is the time of year for reviewing what we have  done and thinking about what we are going to do. It is the time for making plans. The month of January is supposed to be named after the Roman god “Janus” (although there is some dispute about this)

Janus is depicted as having two faces and was the god of doorways and gates. He looked both inwards and outwards, forwards and back. In my opinion Janus is a good character to bear in mind when writing plans and reviews.

In the middle of 2014 I decided that it was high time that I wrote a “Business Plan” for my little business. I’ve done this sort of thing before for other people, but it feels a bit different when it is for yourself.

As it says at the bottom of the front page: 

“Duhallow Grey Geek started without a clear business plan. This document rectifies that. It summarizes the current situation and identifies options. It identifies how tactical plans will be created and provides an outline for the next one to two years.”


Before you start writing (or even researching) any document, it is a good idea to decide who you are writing it for. In this case the answer was: for ME! That’s right – for myself! Of course, I may want to present it to potential investors or business partners but I am the person making the largest investments in terms of effort, time and life. If I think I am going to be wasting my time and effort, I want to find out now, so I can do something more rewarding.


There are plenty of templates for what a business plan should contain, so I won’t share the detailed table of contents. In fact I found myself adding things to the standard contents. Some of the things I included (which you may, or may not, think are “standard”) are:

  • Motivation – Why was I doing this? Why was I excited about it?
  • The current position of the business – In terms of product and sales.
  • Product – What is the product? 
  • Market – Who buys the product?
  • Industry – What is happening in the industry I’m involved in? Where is the growth?
  • What resources and capabilities do I have access to?
  • Constraints – What are the restrictions that I want to apply to the business?

While I was mapping out the contents, I made a list of the questions I wanted to answer and used them as the basis for research. In the end I produced appendix material on:

  • The economics of the industry I am working in (on-line training material)
  • Sales – past performance and future projections
  • Marketing options
  • Alternative sales channels
  • Successful competitors

Predicting future sales is always difficult. In the end, I didn’t try and make predictions. Instead I projected the past performance into the future and then identified the ways I could improve it. I also identified high and low levels which I could use to plan potential investments. 

The “Strategic” document I’ve produced, documents the facts and identifies the options. On the basis of the information available, I’ve picked some things I am going to do (in fact, I’ve started doing them already) and created what I term a “Tactical Plan” for a fixed term. I’ve going to “do the actions” in the Tactical Plan and monitor the results. Towards the end to the period of the plan I will review the results and decide what to do next. 

Wash, rinse, repeat….
  
Is it all going to work? I don’t know. What am I going to do? That would be telling! Keep watching and you’ll find out.