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?