CODEWITHSUNDEEP
mysqlindexingcomparison
Arjun K P
Arjun K P

Reputation: 2111

MySql Query Speed Confusion BETWEEN or many index queries

I've got a situation where i have to select some rows from a table....

The table is somewhat as follows.......

        id           Name          Age

        0011         name1          45
        0012         name2          67
        .
        . 
        .
        .
        0020         name10         100
        .
        .
        .
        .


         // id is a primary key varchar(4).......
         // name is varchar.....
         // age is int.......

Now my need is to get values within a range like 0011 - 0020 or 0020 - 0030..... I can generate the start and end of this paricular range which i would like you not to think about......

But the doubt i've got is

          Should I execute 10 SELECT queries based on the index values ?

for eg:-

           for(i=0;i<10;i++)
           {
                    //SQL Query to SELECT row WHERE id = '0011' LIMIT 0,1 
                    // next time it id to select would be '0012'
            }

OR

           Should I include a single Query to SELECT ROWS using AND, OR , BETWEEN..etc..with limit 10..???

Which one is faster....???

I've got confusion coz in the first case, only 1 row is selected at a time and without much of a operation in each query (where 10 times request is the villain) and in second case where only one query is used, But i believe an overhead is provided on the varchar primary key using comparison operators........

Upvotes: 2

Views: 106

Answers (3)

David
David

Reputation: 1144

You can do sql query to return the results of a table SELECT COUNT(*) FROM table, for example: 1000 And get number of pages $pages=ceil(1000/10);

for ($i=0;$i<=$pages:$i++) {
$start=10*$i;
$sql="SELECT MIN(id), MAX(id) FROM TABLE WHERE LIMIT ".$start.",10";
}

Upvotes: 0

Query Master
Query Master

Reputation: 7097

Single Query is faster way to fetch data from database comparison to other query with for loop they take so much time because each query has own individual execution time now it up to which way you have choose

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

The real cost is parsing and executing multiple queries versus getting more data than you need now, or may need now.

Given you appear to be paging, as in getting ten at a time, you should be seletinga page full at once with a between.

Sometimes a query optimiser may do better with >= Start and <= End than between start and end, but other than that I can't see an issue, unless your table was extremely wide (lot's of data in one row)

Upvotes: 1

Related Questions