CODEWITHSUNDEEP
phpmysqllaravelormlaravel-5
vijaykumar
vijaykumar

Reputation: 4806

MySql limit and offset giving wrong result

I'm getting only 99 records for limit 100 after 9900 offset. Even though i have 2,00,000 records in db with left join in both the tables with distinct. What's wrong with my query or loop

Even i tried the query in phpmyadmin there also it was giving same result 99 records.

Query

select distinct(table1.id), table2.name, table2.uuid from table1
        left join table2 on table1.id = table2.id limit 9900, 100

Laravel Query:

$this
->database
->table('table1')
->selectRaw('distinct(table1.id), table2.uuid, table2.name')
->leftJoin('table1.id', '=', 'table2.id')
->where('opponent_uID', '>', $uID)
->skip($offset)
->take($limit)
->get();

Loop

$limit = 100;
$offset = 0;
while (true) {
     $result = $this->query($limit, $offset);
     $offset += $limit;
     if (empty($result)) {
         break;
     }
     // Logic here
}

Upvotes: 4

Views: 1358

Answers (1)

vijaykumar
vijaykumar

Reputation: 4806

This may not be right answer to address why it was giving the 99 records. But i when i play with that query i found these solutions

Issue:

  1. One main issue with query is i don't have order by.

MySQL MyISAM tables make no guarantees on result with out order by on limit, offsets

Solutions:

  1. Using order by in query Giving 100 results but not tested with entire loop

    select distinct(table1.id), table2.name, table2.uuid from table1 left join table2 on table1.id = table2.id order by table1.id limit 9900, 100

  2. Strangely if i use table2.* instead of some fields in same query with out order by gives 100 records

    select distinct(table1.id), table2.* from table1 left join table2 on table1.id = table2.id order by table1.id limit 9900, 100

So, Finally it's only my assumption for the problem is there might chance of null values in left join tables give wrong result.

Upvotes: 1

Related Questions