CODEWITHSUNDEEP
sqlsql-serversql-server-2005where-clause
Ian G
Ian G

Reputation: 30234

How to use WHERE clauses to optimise SQL query

I have a very simple query. 

SELECT top 100 *
FROM logs
WHERE description LIKE '%5488826%'
ORDER BY id DESC

However it contains a LIKE clause which is searching a largish text field.

I know the table only has (for example) 200200 rows

(I'm only interested in finding something that happened in a row id > 200000).

I tried to optimise the query by adding in

 SELECT top 100 *
 FROM logs
 WHERE  ID > 200000 
 AND description LIKE '%5488826%'
 ORDER BY id DESC

my thinking was that SQL would run the ID > 200000 constraint first and that would help speed up the query, but it still takes minutes to run, what should I do to make this run faster.

EDIT: ID is PK and indexed

Upvotes: 3

Views: 407

Answers (4)

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

I would try subquery first constraint. Depends on DB which you use you can try something like:

SELECT TOP 100 * FROM (
SELECT ID, description FROM logs WHERE ID > 200000
) AS data
JOIN
   logs 
ON
   logs.id = data.id
WHERE
   data.description LIKE '%5488826%'
ORDER BY
   ID DESC

OR (for SQL Server -- with CTE and read uncommitted hint):

;WITH data AS (
    SELECT ID, description FROM logs WITH (NOLOCK) WHERE ID > 200000
)
SELECT
  logs.*
FROM
  data 
  JOIN
    logs WITH (NOLOCK)
  ON
    logs.id = data.id
WHERE
  data.description LIKE '%5488826%'

And make sure you have index on id column.

Upvotes: 0

Saurabh R S
Saurabh R S

Reputation: 3177

See if this works:

SELECT top 100 * from
(select description FROM logs  WHERE  ID > 200000 )  s
where s.description LIKE '%5488826%'  
ORDER BY s.id DESC

Upvotes: 0

Tom Maier
Tom Maier

Reputation: 403

Very short answer ;-) Add an index to the columns.

Upvotes: -1

Tobb
Tobb

Reputation: 12205

You could try to create a temp-table with only the entries with an id > 200000, and query that to see if it makes any difference:

SELECT * INTO #temp FROM logs WHERE id > 200000;

SELECT * FROM #temp WHERE description LIKE '%5488824%' ORDER BY id DESC;

Upvotes: 3

Related Questions