How to uniquely order a MySQL table with a multi column primary key-CodePudding

Good day all

I have a strange query. Let's say I have a table with a composite primary key (2 columns).

CREATE TABLE `testtable` (
  `ifk1` INT(10) NOT NULL,
  `ifk2` INT(10) NOT NULL,
  `data1` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`ifk1`,`ifk2`),
  UNIQUE KEY `keyName` (`data1`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4

Let's add some basic data

INSERT INTO testtable(ifk1 , ifk2 , data1)
VALUES (1 , 2 , 'a') , (5 , 2 , 'b') , (2 , 4 , 'c') , (5 , 8 , 'd') , (2 , 2 , 'e') , (2 , 5 , 'f');

Let's do a simple SELECT to see what order the data comes out in:

ifk1    ifk2       data1
1       2          a
2       2          e
2       4          c
2       5          f
5       2          b
5       8          d

Now, what if I want to write some code to iterate through the table, grabbing X number of records at a time. With a small set of data, this is simple:

SELECT * FROM testtable LIMIT 0 , 2;
SELECT * FROM testtable LIMIT 2 , 2;
SELECT * FROM testtable LIMIT 4 , 2;

This is going to run into some problems as the table gets bigger, as it's not using a WHERE clause and so not using an INDEX. How do I use a WHERE clause to replicate the above SELECTS?

SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2; -- this will work

The first one is easy, but what about the others? Is there a way to do that?

CodePudding user response：

I am not sure I understand the index part of your question. But generally, if you want to iterate over a bigger result set you can use a cursor as described here:

https://www.mysqltutorial.org/mysql-cursor/

This would be for a stored procedure but db drivers for other languages will expose similar functionality.

CodePudding user response：

If your request is to use indexes: Logic is the same:

mysql> SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
 ------ ------ ------- 
| ifk1 | ifk2 | data1 |
 ------ ------ ------- 
|    2 |    4 | c     |
|    5 |    8 | d     |
 ------ ------ ------- 
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------------------- 
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------------------- 
|  1 | SIMPLE      | testtable | NULL       | index | PRIMARY       | keyName | 43      | NULL |    6 |    33.33 | Using where; Using index |
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------------------- 
1 row in set, 1 warning (0.00 sec)


mysql> SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 4,2;
 ------ ------ ------- 
| ifk1 | ifk2 | data1 |
 ------ ------ ------- 
|    2 |    2 | e     |
|    2 |    5 | f     |
 ------ ------ ------- 
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 4,2;
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------------------- 
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------------------- 
|  1 | SIMPLE      | testtable | NULL       | index | PRIMARY       | keyName | 43      | NULL |    6 |    33.33 | Using where; Using index |
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- -------------------------- 
1 row in set, 1 warning (0.00 sec)

Above uses the KeyName indexes: Maybe you should deactivate or drop KeyName index, and enable the query to use PK composite indexes: Please follow these steps to achieve it:

DROP keyName index first:

mysql> ALTER TABLE testtable
    -> DROP INDEX keyName;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

the run the query again to see that PK Composite keys are used in the query which I think make the query faster:

mysql> EXPLAIN SELECT * FROM testtable WHERE ifk1 > 0 AND ifk2 > 0 LIMIT 2,2;
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | testtable | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    6 |    33.33 | Using where |
 ---- ------------- ----------- ------------ ------- --------------- --------- --------- ------ ------ ---------- ------------- 
1 row in set, 1 warning (0.00 sec)

CodePudding user response：

A LIMIT clause without an ORDER BY clause is arbitrary. All three queries you are showing:

SELECT * FROM testtable LIMIT 0 , 2;
SELECT * FROM testtable LIMIT 2 , 2;
SELECT * FROM testtable LIMIT 4 , 2;

could return the exact same two rows. So, you must add an ORDER BY clause to make this work reliably: ORDER BY ifk1, ifk2.

But, yes, having to sort the data again and again for every access can take a lot of time. This is why we try to avoid using offsets and work with a key instead:

SELECT *
FROM testtable 
WHERE ifk1 > @last_ifk1 OR (ifk1 = @last_ifk1 AND ifk2 > @last_ifk2)
ORDER BY ifk1, ifk2
LIMIT 2;

Paging is almost always quite slow. But this access method can use the primary key's unique index on (ifk1, ifk2) and access the next two rows very quickly. It depends on the implemantation in MySQL and its version how fast this is.