Slow query results on 1 million record db
-
I am fairly new to querying results on sql databases. Here is the syntax for my query:
SELECT * FROM companies WHERE (zip=10271||zip=10280||zip=10038||zip=10199||zip=10006) && cat1=1
From what I understand this should be a fairly easy query that is running. This query is passed from a search to a results webpage. There are 1 million records in the database. Eventually I will be passing the zip as a variable from the search to results in order to find any zip code.
I have read up a little on indexing to improve query speeds, but not sure if this is what needs to happen. The results are not coming back extremely slow or anything, but I was wondering if there are simple changes that can be made to improve the speed. If anybody could help it would be greatly appreciated.
-
Bryan,
I'm not sure how you're getting your "||" syntax to work (presumably this is an OR statement?). I've tried this and get syntax errors.
More generally, for this particular query -
When you say there are 1 million rows in the database, how many are there in the Companies table? It is almost certain that a simple non-clustered index on Zip would help this particular query, but it depends on how many matching records there could be.
Could you post some more information, such as table structures and sizes, any existing indexes, etc.
Phil
-
I'm also a little confused with the syntax
and much depends on your table setup. Assuming all the data is in one table then:1) Are the ZipCode field and Cat fields, (at least the Cat1 field), indexed? That would greatly speed up the selection process.
2) Try the syntax:
SELECT * FROM companies WHERE zip in (10271,10280,10038,10199,10006)
and cat1=1
Sql makes data selection a lot easier then 'C' or 'C' syntax languages do.
Good Hunting
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply