How to optimize a SQL query with many thousands of WHERE clauses
I have a series of queries against a very megabyte database and I have hundreds of thousands of ORs in WHERE clauses. What is the best and easiest way to optimize such SQL queries? I've found several articles on creating temporary tables and using joins, but I'm not sure. I'm new to serious SQL, and have cut and paste results from one to the other.
SELECT doc_id, language, author, title FROM doc_text WHERE language='fr' OR language='es'
SELECT doc_id, ref_id FROM doc_ref WHERE doc_id=1234567 OR doc_id=1234570 OR doc_id=1234572 OR doc_id=1234596 OR OR OR ...
SELECT ref_id, location_id FROM ref_master WHERE ref_id=098765 OR ref_id=987654 OR ref_id=876543 OR OR OR ...
SELECT location_id, location_display_name FROM location
SELECT doc_id, index_code, FROM doc_index WHERE doc_id=1234567 OR doc_id=1234570 OR doc_id=1234572 OR doc_id=1234596 OR OR OR x100,000
This non-optimized query can take over 24 hours each. Greetings.
a source to share
I think I just answered my own question ... NON-STATIONARY TABLES!
SELECT doc_text.doc_id, doc_text.language, doc_text.author, doc_text.title, doc_ref.ref_id, ref_master.location_id, location.location_display_name, doc_index.doc_id, doc_index.display_heading
FROM DOC_TEXT, DOC_REF, REF_MASTER, LOCATION, DOC_INDEX
WHERE
doc_text.language='fr' OR doc_text.language='es'
AND
doc_text.doc_id=doc_ref.doc_id
AND
doc_ref.doc_id=ref_master.ref_id
AND
ref_master.location_id=location.location_id
AND
doc_text.doc_id=doc_index.doc_id
a source to share
The easiest way to do it:
- Make indexes on columns that are filtered (
language
,ref_id
,doc_id
etc.), at least, double check their existence. Make them grouped if they are the main index of the table. - Create auxiliary tables that contain conditions (conditions for adding / deleting through INSERT / DELETE statements), also index them.
- instead of 1000 OR components, make an INNER JOIN:
So...
SELECT doc_id, language, author, title
FROM doc_text
WHERE language='fr' OR language='es'
becomes
INSERT language_search (language) VALUES ('fr')
INSERT language_search (language) VALUES ('es')
/* and 50 more */
SELECT dt.doc_id, dt.language, dt.author, dt.title
FROM doc_text dt
INNER JOIN language_search ls ON dt.language = ls.language
a source to share
Instead of having many conditions in one field, you can use the keyword in
:
SELECT doc_id, ref_id FROM doc_ref WHERE doc_id in (1234567, 1234570, 1234572, 1234596, ...)
This will make the queries shorter, but not sure if the performance will be much different. You have to make sure that you have indexes on the appropriate fields, which usually makes a huge difference to performance.
Edit
However, it seems like the reason you have many values to compare is because you are using the result from one query to generate the next. This should of course be resolved with a join instead of a dynamic query:
select
doc_text.doc_id, doc_text.language, doc_text.author, doc_text.title,
doc_ref.ref_id, ref_master.location_id, location.location_display_name,
doc_index.doc_id, doc_index.display_heading
from DOC_TEXT
inner join DOC_REF on doc_text.doc_id = doc_ref.doc_id
inner join REF_MASTER on doc_ref.doc_id = ref_master.ref_id
inner join LOCATION on ref_master.location_id = location.location_id
inner join DOC_INDEX on doc_text.doc_id = doc_index.doc_id
where
doc_text.language in ('fr', 'es')
a source to share
I think your real problem is that you are not using JOIN tables.
That's a guess, but I'm willing to bet that you run a request and then get all the ids in your application and then run another request. WHERE all rows match the previous query. You would greatly improve performance by writing a join query:
SELECT
*
FROM YourTableA a
INNER JOIN YourTableB b ON a.ID=b.ID
WHERE a. .....
will then process a single result set in your application.
a source to share