CODEWITHSUNDEEP
mysqlsql-order-bylimit
doubt
doubt

Reputation: 315

MySQL limit query not able to fetch all data

I have a MySQL MyISAM table whose structure is as below.

CREATE TABLE `VIEW_LOG_TRIGGER_TEMP` (
 `ID` mediumint(9) NOT NULL AUTO_INCREMENT,
 `VIEWER` int(10) unsigned NOT NULL DEFAULT '0',
 `VIEWED` int(10) unsigned NOT NULL DEFAULT '0',
 `DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `SEEN` char(1) NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `VIEWED` (`VIEWED`,`VIEWER`),
 KEY `VIEWER` (`VIEWER`),
 KEY `DATE` (`DATE`)
) ENGINE=MyISAM

enter image description here

The contents of the table are as follows:

enter image description here

I want to get the records between two dates and in each result set I want two records. Executing the following queries whose result set is shown in the attached image:

SELECT * FROM test.VIEW_LOG_TRIGGER_TEMP WHERE DATE >=  '2018-02-11 00:00:00' AND DATE <  '2018-02-12 00:00:00' LIMIT 2;

enter image description here

SELECT * FROM test.VIEW_LOG_TRIGGER_TEMP WHERE DATE >=  '2018-02-11 00:00:00' AND DATE <  '2018-02-12 00:00:00' LIMIT 2,2;

enter image description here

SELECT * FROM test.VIEW_LOG_TRIGGER_TEMP WHERE DATE >=  '2018-02-11 00:00:00' AND DATE <  '2018-02-12 00:00:00' LIMIT 4,2;

enter image description here

As we can see the record with ID 3 is not being fetched. The expected result is all records from the ample should be fetched. If I execute the same queries with the Order by condition on DATE as ORDER BY DATE ASC, I get the required result. Is it that in each fetch we get a new result set on which order is applied?

Upvotes: 0

Views: 526

Answers (2)

vinieth
vinieth

Reputation: 1314

You used ranged Query for Date and your ID is Primary Key. You will get the output based on the Increasing Date and Time Order.

So the order will be in this order if no limit is used

Id
4
2
1
5
3

Limit 2 for Query 1 -> you will get ID's 4 and 2

Limit 2,2 for Query 2-> Here your offset is 2 so it will skip first two result ie.(4 and 2) and It will print ID's 1 and 5

For Query 3 -> Try Limit 4,1 you will get Id 3

Upvotes: 1

Daniel E.
Daniel E.

Reputation: 2480

The problem is the "LIMIT 4,2" and "LIMIT 2,2", it means you don't want all the results

To explain more :

LIMIT 4,2 means you want only 2 results starting from the 4th

And the best explanation for the LIMIT and ORDER optimization from MYSQL website limit optimization

Upvotes: 2

Related Questions