Link to home
Create AccountLog in
Avatar of DBA2000

asked on

Indexing - Multivalued Columns


I have a query that runs against a table (4 mil recs).  That has a multivalued columns - LandLeaseType and Park

Park column has values such as: 'Attached, Direct Entrance, Garage, Gated, Tandem'

LandLeaseType : has values such as: 'Net,Lease,Fee'
and the query looks like this:

and ((Replace((','+mx.LandLeaseType+','),' ','') LIKE Replace (('%,Fee,%'),' ','')))
AND ((Replace((','+m.park+','),' ','') LIKE Replace(('%,Garage - 3 Car,%'),' ','')))

Is it possible to index these two columns?  If not, how can I optimize for these type of search?

Thank you!
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

it is useless to index these columns if you are using like '%something%' on these because a table scan is required to compare each values
Avatar of DBA2000


Thanks, Emoreau

... but can you even index a column that houses multivalues?  Unless, of course, you use fulltext indexing ..

please advise.
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Sorry Eric, this outcome was not my intention.  You had already given the answer.