[MYSQL] ORDER BY moet sneller

[it0]

Zie ook order by optimaliseren in MYSQL

In de search kom ik ook niet veel tergen wat mij een antwoord geeft.

Mijn vraag beste geillustreerd met wat sql outpout

code:

mysql> show index from headers;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| headers |          0 | PRIMARY     |            1 | hid         | A         |      751618 |     NULL | NULL   |         |
| headers |          1 | subject     |            1 | subject     | A         |      751618 |     NULL | NULL   |         |
| headers |          1 | subject     |            2 | msgid       | A         |      751618 |     NULL | NULL   |         |
| headers |          1 | subject     |            3 | groupid     | A         |      751618 |     NULL | NULL   |         |
| headers |          1 | subject     |            4 | date        | A         |      751618 |     NULL | NULL   |         |
| headers |          1 | contenttype |            1 | contenttype | A         |         479 |     NULL | NULL   |         |
| headers |          1 | newssort    |            1 | groupid     | A         |          13 |     NULL | NULL   |         |
| headers |          1 | newssort    |            2 | subject     | A         |      751618 |     NULL | NULL   |         |
| headers |          1 | newssort    |            3 | date        | A         |      751618 |     NULL | NULL   |         |
| headers |          1 | newssort    |            4 | contenttype | A         |      751618 |     NULL | NULL   |         |
| headers |          1 | date        |            1 | date        | A         |      375809 |     NULL | NULL   |         |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---------+
11 rows in set (0.01 sec)
 
mysql> explain select * from headers where groupid='52184' order by subject limit 10;
+---------+-------+---------------+----------+---------+------+--------+------------+
| table   | type  | possible_keys | key      | key_len | ref  | rows   | Extra      |
+---------+-------+---------------+----------+---------+------+--------+------------+
| headers | range | newssort      | newssort |       3 | NULL | 751618 | where used |
+---------+-------+---------------+----------+---------+------+--------+------------+
1 row in set (0.00 sec)
 
mysql> explain select * from headers where groupid='52184' order by msgid limit 10;
+---------+-------+---------------+----------+---------+------+--------+----------------------------+
| table   | type  | possible_keys | key      | key_len | ref  | rows   | Extra                      |
+---------+-------+---------------+----------+---------+------+--------+----------------------------+
| headers | range | newssort      | newssort |       3 | NULL | 751618 | where used; Using filesort |
+---------+-------+---------------+----------+---------+------+--------+----------------------------+
1 row in set (0.00 sec)
 
mysql> explain select * from headers where groupid='52184' order by contenttype limit 10;
+---------+-------+---------------+----------+---------+------+--------+----------------------------+
| table   | type  | possible_keys | key      | key_len | ref  | rows   | Extra                      |
+---------+-------+---------------+----------+---------+------+--------+----------------------------+
| headers | range | newssort      | newssort |       3 | NULL | 751618 | where used; Using filesort |
+---------+-------+---------------+----------+---------+------+--------+----------------------------+
1 row in set (0.01 sec)

Het mag duidelijk zijn dat ik de filesort wil voorkomen. Wat ik niet snap is dat MYSQL de ene keer gewoon de index (order by subject) gebruikt en de andere keer niet (order by contenttype of msgid).

Filesort gaat erg langzaam bij 800000 records terwijl ik er toch minstens tussen de 10 en 20 miljoen records wil hebben.

Hoe komt dit en hoe kan ik het voorkomen?

(Graag niet mijn hoofd breken over select *, dat is puur ter illustratie)

[Verwijderd]

Komt dat niet omdat je die msgid en contenttype allebei in een gecombineerde index hebt zitten? Ik dacht dat je dan ook die volgorde moet aanhouden als in je index.

[ Voor 4% gewijzigd door Verwijderd op 13-03-2003 16:07 ]

[ACM]

ik denk dat je dan toch echt per "selectie en sorterings groepje" een aparte index moet maken.
Dus een op groupid + subject, een op groupid + msgid, een op groupid + contenttype.

Mysql is helaas niet zo'n held in sorteren...
Zelfs voor het sorteren van 3 records kan ie besluiten een filesort te gebruiken

[Glock]

Zie voor de goede indexes ACM en.... als dit nog niet helpt (waarschijnlijk omdat je gewoon mega veel records heb ) moet je deze post maar een doorlezen. Zelf een aparte tabel bijhouden met hetgene wat gesort moet worden scheelt ontiegelijk veel bij MySQL