[mysql] using filesort

[elevator]

Ik heb een database in MySQL waarin ik de logs van bepaalde IRC servers, en IRC channels in opsla. Ik heb een tabel structuur die ziet er (versimpeld) zo uit:

MySQL:

CREATE TABLE ircdata (
  id int(10) unsigned NOT NULL auto_increment,
  ChannelNumber int(11) NOT NULL default '0',
  idinverted int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY idx_chan_idinverted (ChannelNumber,idinverted,id),
) TYPE=InnoDB;

Op zich geen probleem, totdat ik een query wil draaien om de omliggende regels van een bepaalde rowid op te vragen, ik doe dat als volgt:

SQL:

SELECT * FROM ircdata
WHERE (ChannelNumber = 0) AND
(idinverted > 2312213) 
ORDER BY id DESC LIMIT 10;

Het resultaat hiervan is een query die bijna 9 seconde loopt:
10 rows in set (8.51 sec).

Op dat moment, geeft de EXPLAIN hiervan dit als uitkomst:

code:

+---------+-------+---------------------+---------------------+
| table   | type  | possible_keys       | key                 |
+---------+-------+---------------------+---------------------+
| ircdata | range | idx_chan_idinverted | idx_chan_idinverted |
+---------+-------+---------------------+---------------------+


+---------+------+---------+------------------------------------------+
| key_len | ref  | rows    | Extra                                    |
+---------+------+---------+------------------------------------------+
|       8 | NULL | 3721956 | Using where; Using index; Using filesort |
|---------+------+---------+------------------------------------------+

en dan hou je (door de "using filesort") dus geen performance meer over als je database een klein beetje groot is. Het rare is, dat hij wel de juiste index lijkt te gebruiken.

Heeft iemand nog een idee over hoe dit snel te krijgen? Ik ben een klein beetje door de ideeen heen.

Updateje:

Ik heb voor degene die het graag eens willen testten een dump gemaakt van een test(!) database. Hier kan je een bzip2 file downloaden met een dump erin. Deze file is 17 megabyte groot. Normaal gesproken is de database grootte ongeveer 10 keer zo groot, omdat er dus ook de daadwerkelijke chat logs in zitten. Het aantal rows komt wel aardig overeen.

[ Voor 25% gewijzigd door elevator op 17-08-2003 17:29 ]

[curry684]

Wellicht kun je hier iets mee: http://developer.mimer.se...t/Defining_Database9.html

A secondary index is automatically used during searching when it improves the efficiency of the search.

Secondary indexes are maintained by the system and are invisible to the user.

Any column(s) may be specified as a secondary index.

Columns in the PRIMARY KEY, the columns of a FOREIGN KEY and columns defined as UNIQUE are automatically indexed, (in the order in which they are defined in the key), and therefore creation of an index on these columns will not improve performance.

Secondary index tables are purely for Mimer SQL's internal use - you create the index, and Mimer SQL handles the rest. Index names can be made up of a maximum of 128 characters.

If, for instance, you want to know which products were released on a specific date, Mimer SQL would have to search successively through the entire ITEMS table to find all items that matched the date you specified. If, however, you create a secondary index on release date, Mimer SQL would locate that date directly in the secondary index, which would save time.

Secondary indexes can improve the efficiency of data retrieval; but does introduce an overhead for write operations (UPDATE, INSERT, DELETE). In general, you should create indexes only for columns that are frequently searched.

Indexes cannot be created directly on columns in views. However, since searching in a view is actually implemented as searching in the base table, an index on the base table will also be used in view operations.

[ACM]

Een paar losse kreten:
- MySQL kan niet achterstevoren sorteren en zal zelfs met twee records nog een filesort gebruiken daarvoor. Tenzij je dus een workaround hebt om vooruit te sorteren (zoek op topicstarter chem en filesort oid voor een workaround).
- Jouw index is er niet een waarop fatsoenlijk op de id gesorteerd kan worden, dan moet id toch echt vooraan in de index-definitie staan.
- Als idinverted de inverse van id is, dan kan je dus evt _daarop_ sorteren (en dan ASC) voor betere performance bij het sorteren.
- Als de voorgaande waar is, dan is de definitie van je index niet zo zinvol, channelnumber-idinverted is dan voldoende, want de id zelf is net zo uniek als de idinverted, maar als je op channelnumber+id zoekt wordt ie toch alleen voor de channelnumber gebruikt...

[elevator]

Bedankt Ik kon niet eerder reageren wegens wat probleempjes met de bot zelf die eerst opgelost moesten worden voordat ik hiermee doorkon.

Aan de hand van jullie suggesties, heb ik nu de indexen zo gelegd en de queries aangepast: (in deze reeks komt het veld timestamp voor omdat ik ook selecties moet doen op de tijd-range, hier zijn -tot nu toe- nog geen problemen mee dus heb ik die even uit de voorbeeld database gelaten)

idx naam	velden	type index
id	id	primary
idinverted	idinverted	normal
idx_chan_idinverted	channel idinverted	normal
idx_time_channr	timestamp channelnr	normal
idx_chan_id	channel id	normal

Ik voer hier in het totaal een 4 tal queries op uit:

query doel	De laatste 'x' regels van dit channel te zien krijgen
query	EXPLAIN SELECT * FROM ircdata WHERE ( ChannelNumber = 2 ) ORDER BY idinverted ASC LIMIT 50
Explain result	table type possible_keys key key_len ref rows Extra ircdata ref idx_chan_idinverted,idx_chan_id idx_chan_id 4 const 82000 Using where; Using filesort

---

query doel	De bovenliggende 'x' regels boven een bepaalde row
query	EXPLAIN SELECT * FROM ircdata WHERE ( ChannelNumber = 2 ) AND ( idInverted > 4294267294 ) ORDER BY idinverted ASC LIMIT 10
Explain result	table type possible_keys key key_len ref rows Extra ircdata range idinverted,idx_chan_idinverted,idx_chan_id idx_chan_idinverted 8 NULL 21216 Using where

---

query doel	De onderliggende 'x' regels onder een bepaalde row
query	EXPLAIN SELECT * FROM ircdata WHERE ( ChannelNumber = 2 ) AND ( id > 4294967295 - 4294267294 ) ORDER BY id ASC LIMIT 10
Explain result	table type possible_keys key key_len ref rows Extra ircdata ref PRIMARY,idx_chan_idinverted,idx_chan_id idx_chan_idinverted 4 const 110564 Using where; using filesort

---

query doel	De row zelf
query	EXPLAIN SELECT * FROM ircdata WHERE ( idinverted = 4294267294 )
Explain result	table type possible_keys key key_len ref rows Extra ircdata ref idinverted idinverted 4 const 1 Using where

Zoals je ziet, gebruikt ie bij een aantal queries de verkeerde index. Als ik de index hint met USE INDEX() dan pakt hij wel de index, maar blijft MySQL bijzonder lang hangen op "Sending Data" wat dus doet vermoeden dat hij alsnog een table-scan uitvoert.

[ Voor 14% gewijzigd door elevator op 24-08-2003 12:47 ]

[Grijze Vos]

Is het niet netter de logs in text files op te slaan, en de filenames in je database te bewaren?

Just a thought...

[elevator]

Het probleem is dat je hier moeilijk in kan seeken tenzij je met indexen op je textfiles gaat werken. Oftewel - als ik alle IRC rows van channel 5, van Maandag 18 augustus 2003 wil hebben, wordt het met een textfile al een heel stuk moeilijker.

Bovendien kom je al snel in de knoop met locking - op dit moment is er ongeveer 1,5 inserts per seconde, dus dat zou ook 1,5 updates op index files worden.

Met een volledige taal nog niet eens zo'n probleem, maar als je dit probleem wilt oplossen in een taal als PHP oid wordt het allemaal een stuk minder galant

[ Voor 40% gewijzigd door elevator op 25-08-2003 12:50 ]