Mysql – Search multiple values in multiple columns

MySQLperformancequery-performanceselectwhere

I have a table results with this fields:

  • id
  • results_id
  • number_one (int)
  • number_two (int)
  • number_three (int)
  • number_four (int)
  • number_five (int)

Each number_* column can contain a number from 01 to 90.

I have a search form with 90 checkboxes. A user can tick up to 5 checkboxes.

Once a user select n checkbox, I need to build a query that look for a row that contains all those values, but a number can be in any of the five columns.

Example:

a user ticks checkbox 02, 12 and 20. I need to retrieve all the rows with all three number but I a number can be in any of the number_* column. The 20 can be in column number_one, 02 could be in column number_three and 12 in column number_four.
How can I build this query?

Also, would it be better to use one column instead of five columns?
results table:

  • id
  • result_id
  • numbers (string)

Column numbers contains 02-12-20-57-84 where – is the separator.

Example:
a user ticks checkbox 02, 12 and 20. so my query would be

SELECT * 
FROM results 
WHERE numbers LIKE "%02%" 
    AND numbers LIKE "%12%" 
    AND numbers LIKE "%20%";

UPDATE: I am using MySQL at the moment with this setup:

  • id
  • results_id
  • number_one (int)
  • number_two (int)
  • number_three (int)
  • number_four (int)
  • number_five (int)
  • date
  • city (foreign key)

I am planning to move look for optimisation and faster alternatives. I can migrate to any other database just for this specific table. I can also change table structure if needed.
There are around 150k records in that table doing a query similar to:

SELECT *
FROM TableName
WHERE '02' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '12' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '20' IN (Number_1, Number_2, Number_3, Number_4, Number_5)

Will a migration to another DBMS type bring benefits?

Best Answer

Personally I would create your table

  • id
  • result_id
  • value

But with a single value in the value column. Now you can index on the value column and that should dramatically speed things up. Your query would then look like this:

SELECT result_id
FROM TableName
WHERE VALUE IN ('02','12','20')
GROUP BY result_id
HAVING COUNT(1) >= 3

One of the benefits of this is that you can now easily check for 1 match, 2 matches etc. You can also increase the number of items they could check to 50 and your table structure wouldn't have to change.

That being said right now that won't work for you. Right now I would use something like this:

SELECT *
FROM TableName
WHERE '02' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '12' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '20' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
Related Question