I have two different situation, in small DB with 200 Row (So Simple) and in large DB with 2,000,000,000 Row (And adding more rows every day) I want select one or more row so in this case, which one is better query for select?
example table:
ID | code | x_key | group | name | title | other columns |
---|---|---|---|---|---|---|
1 | aa | ak32d | g1 | … | … | … |
200 | zz | zgi32 | g5 | … | … | … |
ID : primary
Code : index (not composite index)
x_Key : index (not composite index)
Group : for example 20 percent of rows in g5 group
so for example this is same table for small and large DB, which QUERY is better to select one or more row? (question mark is mean i put search value in queries)
in Small DB, One Row needed:
SELECT name,title FROM table WHERE code=?
SELECT name,title FROM table WHERE code=? AND x_key=?
SELECT name,title FROM table WHERE code=? AND x_key=? AND group=?
SELECT name,title FROM table WHERE code=? LIMIT 1
SELECT name,title FROM table WHERE code=? AND x_key=? LIMIT 1
SELECT name,title FROM table WHERE code=? AND x_key=? AND group=? LIMIT 1
in Small DB, More than one Row needed:
SELECT name,title FROM table WHERE group=?
SELECT name,title FROM table WHERE group=? AND name LIKE `%test`
&&
in Large DB, One Row needed:
SELECT name,title FROM table WHERE code=?
SELECT name,title FROM table WHERE code=? AND x_key=?
SELECT name,title FROM table WHERE code=? AND x_key=? AND group=?
SELECT name,title FROM table WHERE code=? LIMIT 1
SELECT name,title FROM table WHERE code=? AND x_key=? LIMIT 1
SELECT name,title FROM table WHERE code=? AND x_key=? AND group=? LIMIT 1
in Large DB, More than one Row needed:
SELECT name,title FROM table WHERE group=?
SELECT name,title FROM table WHERE group=? AND name LIKE `%test`
In small and large which one is better for SELECT, and even for UPDATE?
2
Answers
First of all, the queries with
LIMIT 1
statement/part are faster.The speed of the remaining parts of query depends on which columns are configured to be indexed.
If all are indexed (or at least those accessed), then know that filtering with PHP is slower, hence the query with most conditions would be fastest.
But if the
code
column isunique
, then not checking remaining columns would be fastest:You say "One Row needed" — Do you mean "only one row will be returned"? Or "I need only one of the rows, any row will suffice"? Or "I need only the first row in some order"?
Having these two ‘composite’ indexes will help all the queries you mentioned.
If either of those combinations is unique, then consider making it the
PRIMARY KEY
and get rid ofID
. That will speed up some of the queries even more. You are implying thatcode
, by itself isUNIQUE
. In that case, get rid ofID
and have onlyIn particular, my previous 3-column suggestion is not worth having.
You may have trouble measuring any improvement in the 209-row test case, but larger, even 2B-row, tables will benefit.
The single-row indexes may as well be DROPped.
LIMIT 1
is not useful if there is only one row; leave it off unless you are unsure. If there are multiple rows, be aware that, withoutORDER BY
, you get whichever row the Optimizer feels like giving you.Adding an
ORDER BY
requires going back to the drawing board for index design. Much of that is covered in my Index CookbookAll of the above applies to
UPDATE
, too. However, if you need to update more than a few hundred rows at once, there are other issues.