CODEWITHSUNDEEP
sqlviewgroup-bysql-like
John
John

Reputation: 11

How to get SQL to use where criteria when using group by and like statements

I have a SQL view that contains a group by. I am trying to select from the view using a like. The column I am selecting on is indexed but SQL insists on creating a temp table with all the rows in the table and then filtering on the where criteria. (slow) How do I get it to filter FIRST?

Example:

View definition:

SELECT ListCode, SUM(CASE
    WHEN ListStatus = 'A' THEN 1
      ELSE 0
      END) Active
FROM ListParticipation
GROUP BY ListCode

Select:

SELECT *
FROM ListParticipationView
WHERE ListCode like '%ReallyCoolList%'

Incidentally, if I use like criteria without the beginning wildcard the SQL plan does show the filtering occurring before the group summarization.

Upvotes: 1

Views: 563

Answers (3)

HLGEM
HLGEM

Reputation: 96570

I see you've learned why views are often a bad idea. It gets much worse if they call other views. If the SQL statement directly accessing the table is faster, use that. But don't use a where clause with a like statement that has a wildcard as the first character. Doing this for anythign other than a throw-away query indicates you need to use a differnt technique such as:

  1. youneed to fix the design
    or
  2. you need to use fulltext indexing

If you are storing data such as 'test, mytest, another stupid test' in one column and using the like clause to find all values of 'mytest' then you need to normalize your tables and store the data correctly.

If you have users who are searching for terms they may not know the exact name of (say a long formal airport name like 'Ronald Reagan National Airport' and the searchmight be for 'National Airport') then use full text indexing instead.

If you are putting the % in front for no particular reason, then stop doing it.

Upvotes: 0

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

It's not filtering first because the beginning wildcard is preventing the index from being used. Basically, it has to search the entire string - so it still has to search every single row, because the index on a char or varchar usually starts from the first character... This would be roughly equivalent to wondering why a multiple-column index isn't being used, when your selection criteria only restricts based on the second or third column, not the first.

If you have repeating ListCodes (as would apparently be the case), consider extracting them to their own table, then placing the id of the new table as listCodeId in the ListParticipation table (and replace the index too). If you have a smart enough optimizer, it'll find all the listCodes in their table that match the given wild-carded string (should be unique, so please have a unique constraint), then use that to query the fk index on listCodeId. Should be much more performant.

Upvotes: 1

JNK
JNK

Reputation: 65157

Your criteria is not SARGable, that is it cannot use an index.

Using LIKE with % at the beginning of the comparison string guarantees a table scan. SQL has to check the whole field in every row to evaluate for a match.

If your ListCode is a very long string, maybe you should make it an int which is the PK in a lookup table. Then you could evaluate:

WHERE Listcode IN (1, 3, 4, 6) and make use of an index.

Upvotes: 2

Related Questions