Query is too Complex Error in SQL

Anon

I've got the following query, which is huge. As I just found out, that I can't use more than 40 ORs in a Where clause I get the 'Query ids too Complex Error'. Although, I can't figure out how to change the Query, in order to split it up into 2 WHERE clauses or something else.
Has anybody an idea how to modify this Query?

Thanks]

$sql_query3 = "SELECT * FROM dbtable WHERE (index LIKE '%$search_str%'".
" OR title LIKE '%$search_str%'OR author LIKE '%$search_str%'".
" OR summary LIKE '%$search_str%' OR source LIKE '%$search_str%'".
" OR theme1 LIKE '%$search_str%' OR theme2 LIKE '%$search_str%'".
" OR theme3 LIKE '%$search_str%' OR theme4 LIKE '%$search_str%'".
" OR theme5 LIKE '%$search_str%' OR organisation LIKE '%$search_str%'".
" OR organisation2 LIKE '%$search_str%'".
" OR organisation3 LIKE '%$search_str%'".
" OR organisation4 LIKE '%$search_str%'".
" OR organisation5 LIKE '%$search_str%'".
" OR location LIKE '%$search_str%' OR hyperlink LIKE '%$search_str%'".
" OR entry_date LIKE '%$search_str%'".
" OR update_date LIKE '%$search_str%'".
" OR document_type LIKE '%$search_str%'".
" OR archive LIKE '%$search_str%'".
" OR synthesis LIKE '%$search_str%'".
" OR comments LIKE '%$search_str%'".
" OR priority LIKE '%$search_str%'".
" OR synthesis_flag LIKE '%$search_str%') OR (index LIKE '%$search_str2%'".
" OR title LIKE '%$search_str2%'OR author LIKE '%$search_str2%'".
" OR summary LIKE '%$search_str2%' OR source LIKE '%$search_str2%'".
" OR theme1 LIKE '%$search_str2%' OR theme2 LIKE '%$search_str2%'".
" OR theme3 LIKE '%$search_str2%' OR theme4 LIKE '%$search_str2%'".
" OR theme5 LIKE '%$search_str2%' OR organisation LIKE '%$search_str2%'".
" OR organisation2 LIKE '%$search_str2%'".
" OR organisation3 LIKE '%$search_str2%'".
" OR organisation4 LIKE '%$search_str2%'".
" OR organisation5 LIKE '%$search_str2%'".
" OR location LIKE '%$search_str2%' OR hyperlink LIKE '%$search_str2%'".
" OR entry_date LIKE '%$search_str2%'".
" OR update_date LIKE '%$search_str2%'".
" OR document_type LIKE '%$search_str2%'".
" OR archive LIKE '%$search_str2%'".
" OR synthesis LIKE '%$search_str2%'".
" OR comments LIKE '%$search_str2%'".
" OR priority LIKE '%$search_str2%'".
" OR synthesis_flag LIKE '%$search_str2%') ORDER BY update_date DESC";

Anon

Gak! That is one ugly SELECT statement! Even if you were able to get it to run, the performance is going to be terrible due to the leading wildcards ('%___') not allowing index usage.

To answer your question, you could use a UNION operator. For example:
$sql_query3 = "SELECT FROM dbtable where".
" index LIKE '%$search_str%'".
" OR title LIKE '%$search_str%'OR author LIKE '%$search_str%'".
" OR summary LIKE '%$search_str%' OR source LIKE '%$search_str%'".
" OR theme1 LIKE '%$search_str%' OR theme2 LIKE '%$search_str%'".
" OR theme3 LIKE '%$search_str%' OR theme4 LIKE '%$search_str%'".
"UNION ".
"SELECT FROM dbtable where".
" theme5 LIKE '%$search_str%' OR organisation LIKE '%$search_str%'".
" OR organisation2 LIKE '%$search_str%'".
" OR organisation3 LIKE '%$search_str%'".
" OR organisation4 LIKE '%$search_str%'".
...etc...

Unfortunately that doesn't improve search times if dbtable is large. Another approach would be to keep a separate table for search keys. Assuming the primary key on dbtable is a number, then:

CREATE TABLE dbtable_keys
(dbtable_record_id NUMBER,
key_value VARCHAR(___));

On INSERT into dbtable:
INSERT INTO dbtable_keys VALUES (dbtable_primary_key, dbtable_index);
INSERT INTO dbtable_keys VALUES (dbtable_primary_key, dbtable_title);
INSERT INTO dbtable_keys VALUES (dbtable_primary_key, dbtable_author);
INSERT INTO dbtable_keys VALUES (dbtable_primary_key, dbtable_summary);
INSERT INTO dbtable_keys VALUES (dbtable_primary_key, dbtable_source);
...etc...

To search for a string then becomes:
$sql_query = "SELECT *"
" FROM dbtable"
" WHERE dbtable_primary_key IN (SELECT dbtable_record_id".
" FROM dbtable_keys"
" WHERE key_value LIKE '%$search_str%')";

Still not ideal performance, but greatly improved over 40+ full table scans.

-- Michael
Darkstreak Computing & Innovations
www.darkstreak.com

[deleted]

My first thought would be....

AAAAARGH! :-)

How many fields are you searching in here?
Nevermind, I don't _want) do know.

You do know that LIKE is the most expensive method of comparing data?
ie: this query does a substring-search on the entire table.

Ouch.

One very obvious way of splitting this up is by doing two queries, one searches for search_str and the other for search_str2.
Then you can combine the outputs in PHP (ort a UNION in proper RDBMS's )and present the results.

Another, more complex method to search lots of columns at once is to store the unique words from the data in those columns in a new table, and search that table instead.

Read this article:
http://www.phpbuilder.com/columns/clay19990421.php3

All you need is to know which rows a word appeared in. So you only have to get all the unique words from each row and store them in a table, along with the row's id.
Then you can search that table for a word/words and get the rowid's in a very simple query.

Anon

Thanks a lot for your help!! and thanks for the optimization tips!!

Patrick