[Solved] Check if String starts with column value with best performance

EverSQL Database Performance Knowledge Base

Check if String starts with column value with best performance

I currently have this table (myTable) in my database:

user             start
----------------------------
Adam             12345
Alex             123
Benny            2345

In my program, I accept a string from user, eg: 12345678

My objective is to select out the row where user input starts with myTable.Start

-- For example, it would be great to have something like:

select * from myTable where "12345678".startsWith(start)

-- and returns me Adam, 12345 & Alex, 123

As of now I'm using 

select user, start
from myTable where charindex(start, "12345678") = 1 
order by start desc

which does the job, but in absolute terrible performance, myTable row count is about near a million, I'm not sure if indexing start would help as I'm not doing a direct compare in this case.

Does anyone know a better way to accomplish this?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Calling Functions With Indexed Columns (query line: 7): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `start` is indexed, the index won’t be used as it’s wrapped with the function `charindex`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  2. Avoid Calling Functions With Indexed Columns (query line: 7): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `12345678` is indexed, the index won’t be used as it’s wrapped with the function `charindex`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  3. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `myTable` ADD INDEX `mytable_idx_start` (`start`);
The optimized query:
SELECT
        myTable.user,
        myTable.start 
    FROM
        myTable 
    WHERE
        charindex(myTable.start, myTable."12345678") = 1 
    ORDER BY
        myTable.start DESC

Related Articles



* original question posted on StackOverflow here.