[mysql] trage order by, hoe indexen leggen?

[twiekert]

ik ben bezig om een website te maken waarop gebruikers hun advertenties kunnen plaatsen.
op dit moment ben ik aan het testen met een behoorlijk aantal advertenties (6100). hierop laat ik een query los die alle advertenties moet ophalen en sorteren.

de volgende (ingekorte) query haalt alle advertenties + koppeling naar categorie en merk op en sorteert op categorie, merk en typenummer:

code:

SELECT SQL_NO_CACHE DISTINCT 
  cat.omschrijving AS cat, 
  cat.id AS catid, 
  merk.omschrijving AS merk, 
  advertentie.typenummer
FROM 
  cat, 
  merk, 
  advertentie, 
  gebruiker 
WHERE
  cat.id = advertentie.cat_id 
  AND merk.id = advertentie.merk_id 
  AND advertentie.zichtbaarheid IN (0,1,2,5) 
  AND gebruiker.id = advertentie.gebruiker_id 
  AND gebruiker.accountmode != 2 
ORDER BY 
  cat.omschrijving, 
  merk.omschrijving, 
  advertentie.typenummer
LIMIT 0, 25

nu duurt deze query 0,71 seconden volgens mysql, dit vindt ik nogal traag. als ik de orderby weglaat dan duurt de query 0.00-0.01 seconden.

explain select met ORDER BY:

code:

+-------------+--------+-------------------------------------------+--------------+---------+--------------------------+------+----------------------------------------------+
| table       | type   | possible_keys                             | key          | key_len | ref                      | rows | Extra                                        |
+-------------+--------+-------------------------------------------+--------------+---------+--------------------------+------+----------------------------------------------+
| merk        | index  | PRIMARY                                   | omschrijving |      50 | NULL                     |  101 | Using index; Using temporary; Using filesort |
| advertentie | ref    | gebruiker_id,merk_id,cat_id,zichtbaarheid | merk_id      |       4 | merk.id                  |   30 | Using where                                  |
| cat         | eq_ref | PRIMARY                                   | PRIMARY      |       4 | advertentie.cat_id       |    1 |                                              |
| gebruiker   | eq_ref | PRIMARY                                   | PRIMARY      |       4 | advertentie.gebruiker_id |    1 | Using where; Distinct                        |
+-------------+--------+-------------------------------------------+--------------+---------+--------------------------+------+----------------------------------------------+

explain select zonder ORDER BY:

code:

+-------------+--------+-------------------------------------------+--------------+---------+--------------------------+------+------------------------------+
| table       | type   | possible_keys                             | key          | key_len | ref                      | rows | Extra                        |
+-------------+--------+-------------------------------------------+--------------+---------+--------------------------+------+------------------------------+
| merk        | index  | PRIMARY                                   | omschrijving |      50 | NULL                     |  101 | Using index; Using temporary |
| advertentie | ref    | gebruiker_id,merk_id,cat_id,zichtbaarheid | merk_id      |       4 | merk.id                  |   30 | Using where                  |
| cat         | eq_ref | PRIMARY                                   | PRIMARY      |       4 | advertentie.cat_id       |    1 |                              |
| gebruiker   | eq_ref | PRIMARY                                   | PRIMARY      |       4 | advertentie.gebruiker_id |    1 | Using where; Distinct        |
+-------------+--------+-------------------------------------------+--------------+---------+--------------------------+------+------------------------------+

het ligt dus aan die filesort, maar hoe zorg ik ervoor dat mysql geen filesort nodig heeft en is dat zoiezo wel mogelijk?

table advertenties
id - primary key, auto inc
typenummer (INDEX) (char 30)
cat_id (FK, INDEX) (int)
merk_id (FK, INDEX) (int)
gebruiker_id (FK, INDEX) (int)

table cat
id - primary key, auto inc
omschrijving (char 50)

table merk
id - primary key, auto inc
omschrijving (char 50)

table gebruiker
id - primary key, auto inc
gebruikersnaam (char 50)


aan de andere kant kan het natuurlijk ook aan de machine liggen, dit is namelijk een p3-500, 384 mb intern geheugen met op dit moment 17mb vrij.
scsi hdd 9gb 10K
draait linux2.4.20, 3 webservers, mysql 4.012.

deze machine wordt in principe alleen gebruikt voor de database en webserver van deze site. er draaien voor de rest geen processor vretende applicaties.
is deze snelheid normaal of heb ik ergens een index niet goed liggen

[whoami]

Het kan verbeteren met een index, maar een order by zal AFAIK altijd trager zijn dan als je hem weglaat.
Het DBMS moet nl. alle records gaan ophalen en dan sorteren, terwijl, als je die ORDER BY weglaat, niet alles in 1x moet gefetched worden.

[bigtree]

Het klinkt raar, maar als je de volgorde van je WHERE clauses verandert, kan het ook sneller. Aangezien bijvoorbeeld voor tabel 'advertentie' als eerste veld 'merk_id' genoemd wordt, negeert MySQL de andere indexen op die tabel. En dat terwijl de belangrijkste index op die tabel (in deze query) het veld 'zichtbaarheid' is. Zorg er ten eerste dus voor dat MySQL die index gebruikt (zie kolom 'key' in je EXPLAIN achter advertentie).

Daarnaast; voor de tabel 'merk' wordt niet de primaire sleutel gebruikt om de records mee op te halen -> dat is fout. Aangezien de tabel advertentie leading is (hier vindt het feitelijke filteren op plaats), is de koppeling met de tabel 'merk' een gewone INNER JOIN op de primaire sleutel. De records moeten dus uit de tabel 'merk' worden gehaald op basis van de primaire sleutel en pas daarna worden gesorteerd op 'omschrijving'. Zelf geef ik de voorkeur aan de INNER JOIN syntax omdat je daar dit soort ellende meestal mee voorkomt, maar in jouw geval kan je het waarschijnlijk ook oplossen door de volgorde van de tabellen in je FROM clause om te draaien; advertentie moet eerst.

[twiekert]

nou, inner join gebruiken of the tabellen / where clauses in andere volgorde zetten bood ook geen soelaas

wat ik wel heb geprobeerd is voor de tabel advertentie de kolom zichtbaarheid als index forceren:

code:

SELECT SQL_NO_CACHE DISTINCT
  cat.omschrijving AS cat,
  cat.id AS catid,
  merk.omschrijving AS merk,
  advertentie.typenummer
FROM
  advertentie FORCE INDEX(zichtbaarheid),
  cat,
  merk,
  gebruiker
WHERE
  advertentie.zichtbaarheid IN (0,1,2,5)
  AND cat.id = advertentie.cat_id
  AND merk.id = advertentie.merk_id
  AND gebruiker.id = advertentie.gebruiker_id
  AND gebruiker.accountmode != 2
ORDER BY
  cat.omschrijving,
  merk.omschrijving,
  advertentie.typenummer
LIMIT 0, 25

code:

+-------------+--------+---------------+---------------+---------+--------------------------+------+----------------------------------------------+
| table       | type   | possible_keys | key           | key_len | ref                      | rows | Extra                                        |
+-------------+--------+---------------+---------------+---------+--------------------------+------+----------------------------------------------+
| advertentie | range  | zichtbaarheid | zichtbaarheid |       1 | NULL                     | 3107 | Using where; Using temporary; Using filesort |
| cat         | eq_ref | PRIMARY       | PRIMARY       |       4 | advertentie.cat_id       |    1 |                                              |
| merk        | eq_ref | PRIMARY       | PRIMARY       |       4 | advertentie.merk_id      |    1 |                                              |
| gebruiker   | eq_ref | PRIMARY       | PRIMARY       |       4 | advertentie.gebruiker_id |    1 | Using where; Distinct                        |
+-------------+--------+---------------+---------------+---------+--------------------------+------+----------------------------------------------+
4 rows in set (0.00 sec)

nu dus wel in de goeie volgorde maar de exec time is nog steeds 0.76

[TeeDee]

Call me stupid, maar is die SQL_NO_CACHE nodig?

[twiekert]

die is wel nodig ja. ik heb het cachen van de result van een query in mysql aangezet. voor deze query is dat onhandig, data veranderd heel snel dus ik cache em gewoon niet

[whoami]

Check m'n eerdere bericht. Het heeft te maken met het fetchen van de data.

[twiekert]

whoami schreef op 16 May 2003 @ 14:35:
Check m'n eerdere bericht. Het heeft te maken met het fetchen van de data.

met andere woorden: er is niets aante doen

als ik het goed begrijp doet de database dit:

maak result set waarbij zichtbaarheid 0, 1 ,2 of 5 is, en waar een koppeling bestaat met merken en categorie, en waar de gebruiker van de advertentie geen accountmode van 2 heeft.

resultaat is dan: Er zijn 6108 advertentie(s) gevonden.

mysql doet sorteren, en limit de bende op 0,25.

maar nou de vraag; is de execution time van 0,76 wel normaal voor het sorteren van 6108 records op een p3-500

zoja, dan maar is m'n werkgever lief aankijken voor een snellere bak of anders dit soort queries met een grote result set maar vermijden.

[nescafe]

probeer nu eens beide queries ZONDER limit.
Bij limit zonder order by hoeft ie alleen maar de eerste x records op te halen.
Bij limit met order by haalt ie eerste alle records op, sorteert ze, en doet dan pas een limit.

Dit is iig mijn interpretatie van [rml]whoami in "[ mysql] trage order by, hoe indexen leggen"[/rml]

[bigtree]

Wat je ook kan proberen is gebruik te maken van de index die al ligt op cat.omschrijving. Als je de records uit advertentie ophaalt volgends de volgorde van cat.omschrijving, is het misschien sneller:

code:

SELECT SQL_NO_CACHE DISTINCT
  cat.omschrijving AS cat,
  cat.id AS catid,
  merk.omschrijving AS merk,
  advertentie.typenummer
FROM
  cat
INNER JOIN advertentie ON advertentie.cat_id = cat.id
INNER JOIN merk ON merk.id = advertentie.merk_id
INNER JOIN gebruiker ON gebruiker.id = advertentie.gebruiker_id

WHERE
  advertentie.zichtbaarheid IN (0,1,2,5)
  AND gebruiker.accountmode != 2
ORDER BY
  cat.omschrijving,
  merk.omschrijving,
  advertentie.typenummer
LIMIT 0, 25

[twiekert]

nescafe schreef op 16 mei 2003 @ 15:01:
probeer nu eens beide queries ZONDER limit.
Bij limit zonder order by hoeft ie alleen maar de eerste x records op te halen.
Bij limit met order by haalt ie eerste alle records op, sorteert ze, en doet dan pas een limit.

Dit is iig mijn interpretatie van [rml]whoami in "[ mysql] trage order by, hoe indexen leggen"[/rml]

jep dat klopt als ik alle records ophaal (6108) doet tie er evenlang over. dus er valt weinig aan te doen behalve een snellere bak regelen of dit soort queries te vermijden.

bigtree schreef op 16 mei 2003 @ 15:02:
Wat je ook kan proberen is gebruik te maken van de index die al ligt op cat.omschrijving. Als je de records uit advertentie ophaalt volgends de volgorde van cat.omschrijving, is het misschien sneller

*query*

maakt helaas niet uit.

als ik trouwens de index bij advertentie forceer op zichtbaarheid dan duurt de query 0.06 sec langer, mysql optimaliseert de query zelf dus best behoorlijk

[bigtree]

twiekert schreef op 16 May 2003 @ 15:19:
[...]dus er valt weinig aan te doen behalve een snellere bak regelen of dit soort queries te vermijden. [...]

Vooral dat laatste lijkt me ook vanuit gebruikers-standpunt een goede optie. Ik ga bijvoorbeeld echt niet 6100 advertenties bekijken in brokken van 25 stuks (of ze nou op alfabetische volgorde staan of niet ).