Order and pagination
I have a thousand records in my database mysql
and I am using pagination to get 10 results in total.
When I add order by
to my query it slows down but when I omit it the query is very fast.
I know the problem is because the query loads the whole results, sorts them, and after that gets 10 records.
I am not using an index because the use of the column for the order is PK and I think that if I am not mistaken in mysql
, the index is created automatically on each primary key
- Why is there an index on my pc that is the column i am ordering. not used?
- Is there an alternative solution for sorting without loading all the data?
- How do I add new inserted data to the first row of tables and not to the end of the table?
My sql query
select distinct ...... order by appeloffre0_.ID_APPEL_OFFRE desc limit 10
and my indices
mysql> show index from appel_offre;
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| appel_offre | 0 | PRIMARY | 1 | ID_APPEL_OFFRE | A | 13691 | NULL | NULL | | BTREE | | |
| appel_offre | 1 | appel_offre_ibfk_1 | 1 | ID_APPEL_OFFRE_MERE | A | 2 | NULL | NULL | YES | BTREE | | |
| appel_offre | 1 | appel_offre_ibfk_2 | 1 | ID_ACHETEUR | A | 2 | NULL | NULL | | BTREE | | |
| appel_offre | 1 | appel_offre_ibfk_3 | 1 | USER_SAISIE | A | 2 | NULL | NULL | YES | BTREE | | |
| appel_offre | 1 | appel_offre_ibfk_4 | 1 | USER_VALIDATION | A | 4 | NULL | NULL | YES | BTREE | | |
| appel_offre | 1 | ao_fk_3 | 1 | TYPE_MARCHE | A | 2 | NULL | NULL | YES | BTREE | | |
| appel_offre | 1 | ao_fk_5 | 1 | USER_CONTROLE | A | 2 | NULL | NULL | YES | BTREE | | |
+-------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.03 sec)
in the cmd explanation no index was selected:
+----+-------------+---------------+--------+-------------------------------------+--------------------+---------+----------------
| id | select_type | table | type | possible_keys | key | key_len | ref
+----+-------------+---------------+--------+-------------------------------------+--------------------+---------+----------------
| 1 | SIMPLE | appeloffre0_ | ALL | NULL | NULL | NULL | NULL
UPDATE SOLUTION
the problem was from distinct
when i remove it when the final uses that index.
source to share
Since you are already using the index on "USER_VALIDATION", MySQL will not use the index ID.
Try rebuilding the USER_VALIDATION index to include the ID:
CREATE UNIQUE INDEX appel_offre_ibfk_4 ON appel_offre (USER_VALIDATION, ID);
Update
Log all Hibernate queries, retrieve the slow query and use EXPLAIN in the db console to understand which execution plan MySQL chooses for this query.Maybe the db will be able to use FULL TABLE SCAN even if you have an index because the index is too big to fit into memory. Try to give him a TIP as described in this post .
According to MySQL documentation on ORDER BY optimization, you should:
To increase the speed of ORDER BY, check if you can force MySQL to use indexes and not an additional sorting phase. If this is not possible, you can try the following strategies:
• Increase the value of the sort_buffer_size variable.
• Increase the value of the read_rnd_buffer_size variable.
• Use less RAM for each row, declaring columns only as large as necessary to hold the values stored in them. For example, CHAR (16) is better than CHAR (200) if the values are less than 16 characters.
• Change the tmpdir system variable to point to a dedicated system file with a lot of free space. The value of a variable can be displayed in several ways, which are used in a circular fashion; you can use this to distribute the load across multiple directories. Paths must be separated by colons (":") on Unix characters and semicolons (";") on Windows, NetWare and OS / 2. The paths must point to directories in file systems located on different physical disks, no different partitions on the same disk.
Also make sure DISTINCT doesn't override your index. Try removing it and see if it helps.
source to share
- Add an index to the column you are ordering.
- You cannot add rows to the beginning of a table, just as you cannot add rows to the end of a table. Database tables are multisets. Multisets are, by definition, unordered collections. The notion of first element or last element is meaningless for multisets.
source to share