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 : 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?

CodePudding user response：

First of all, the queries with LIMIT 1 statement/part are faster.

But don't forget ORDER BY, else MySQL will choose what subset to return (as mentioned in comments).

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 is unique, then not checking remaining columns would be fastest:

SELECT name, title FROM table WHERE code=?

You get the idea, if any of your columns is unique, set condition only for that.