I have a table. specs[id,name,store_id,section_id,category_id,user_id,model_id]
My query is very simple:
SELECT * FROM specs
WHERE
store_id=1
AND
section_id=1
AND
category_id=1
AND
user_id=1
AND
model_id=1
What I receive in result, is a list of specs.
If I will change the query to:
SELECT * FROM specs WHERE model_id=1
I will get the SAME results, since the table can have duplicates (excluding the id
, of-course)
Data sample (note that :
id | name | model_id | section_id |category_id | user_id
------------------------------------------------------------------
1 | me | 22 | 5 | 11 | 777
2 | you | 22 | 5 | 11 | 777
3 | me | 22 | 5 | 11 | 777
4 | me | 22 | 5 | 11 | 777
5 | you | 33 | 5 | 11 | 777
6 | they | 33 | 5 | 11 | 777
7 | them | 33 | 5 | 11 | 777
8 | we | 33 | 5 | 11 | 777
9 | we | 33 | 5 | 11 | 777
10 | them | 44 | 5 | 11 | 776
11 | they | 44 | 5 | 11 | 776
12 | me | 44 | 5 | 11 | 776
13 | you | 44 | 5 | 11 | 776
14 | they | 44 | 5 | 11 | 776
15 | them | 44 | 5 | 11 | 776
The DB is MySQL.
The question: since the table will contain 500k-1000k records, would it be faster to use the first query or the second? more conditions will make it faster? slower?
edit
When a record is being added, the user must supply a value for ALL columns. and the records are ALWAYS attached to a model_id.
So it doesn't really matter what other conditions are supplied, since the model is the key.
Here's one of my older answer on multi-column where clauses and indexes in MySQL.