mysql: select with lots of OR's exceeds max execution time

[partha]

here's what I'm trying to do:
I have a form full of about a hundred checkboxes. I want the user to be able to click certain checkboxes to "search" for in a mysql database.

so I wrote a select statement that's like:
SELECT * FROM table WHERE checkbox1 = 1 OR checkbox2 = 1 OR checkbox3 = 1, etc
but when I try to run it, it exceeds the max execution time of 30secs.

[bibby]

Up the time.
Search php.ini for max_execution_time
and set it to whatever works.

There was a time I was making a map generator and bumped it up to 20 minutes, since that was about as long as it took.

Even 30 sec is a long enough wait.

[Stooshie]

It might be quicker (although I haven't tried it) if you created a SQL string thus:

$q = "";
for($i=1; $i<=$numCheckBoxes; $i++)
{
if($_POST["checkbox".$i]==1)
{
if($i<$numCheckBoxes)
$q .= "SELECT * FROM tab WHERE checkbox".$i."=1 UNION ";
}
else
{
$q .= "SELECT * FROM tab WHERE checkbox".$i."=1";
}
}

[edited by: Stooshie at 3:41 pm (utc) on Aug. 19, 2005]

[hughie]

That doesn't sound like a query that should take more than 30 seconds, are you sure that's the problem?

Cheers,
hughie

[Stooshie]

That doesn't sound like a query that should take more than 30 seconds, are you sure that's the problem?

Queries with OR always take a while. With the equivalent AND query the subset is constantly reduced. With an OR query, the amount of time each OR takes is proportional to the number of records.

[hughie]

thats a BIG query then! didn't realise that.

Surely there is a better way of searching on 100 1/0 fields in one go, going to really annoy the user and hog resources if it's used a lot.

When i store checkboxes in table i treat them as a binary number:

Likes Cod - Check
Likes Beef -
Likes Pork - Check
Likes Cheese - Check
Likes milk - Check

Which becomes: 11101
Which i then convert to a decimal: 29
and store as an integer.

I've not done any long queries or dealt with 100 of them but i would think it would be a more managable way of breaking down the search initally.

So in a search if someone says they want to see all people who like pork and cheese (bin: 00110 Dec: 12)then you could search all those with the integer>12 which cuts down the search.

if i'm barking up the right tree i'll dig a little deeper if required, might be completely the wrong way of doing it ;-)

Cheers,
hughie

[Stooshie]

... search all those with the integer>12 ...

That would be fine until someone ticks "likes milk" which would be greater than 12 but would pass the "likes pork and cheese" test.

You would need to do an AND operation on the number created from the users checkbox selection with the number that existed in the db table (created from the fields checkbox1 etc...). Then check the result of that operation for >0.

So, using Hughie's example you would have the user's input as 11101.

So the SQL would have to be someting like (not syntactically correct):

SELECT *
FROM table
WHERE (users_input & field_number)>0

I'm not entirely sure that can be done in SQL.

You could also bring back all of the rows in the table, loop round each row, carry out the AND operation and add the ones that return a value >0 to an array.

Hope it helps.

Best regards,

Andrew Wilson.

[hughie]

good point, got my maths wrong as usual

You would need to find out the maximum a set of all 1's would be from "likes milk" down then search on that integer.

this would cut out the values below but not if someone has "likes moose" 100000 and nothing else.

probably not very satisfactory ;-)

cheers,
hughie