Question 22240 in Mysql Question posted in
The official MySQL documentation can be found here.

SELECT QUERY With index in small & large DB – Mysql

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    INDEX(code, x_key, group)
INDEX(group, name)

    If either of those combinations is unique, then consider making it the PRIMARY KEY and get rid of ID. That will speed up some of the queries even more. You are implying that code, by itself is UNIQUE. In that case, get rid of ID and have only

     PRIMARY KEY(code),
 INDEX(group, name)

    In 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, without ORDER 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 Cookbook

    All 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search