[MySQL] Query optimalisatie (langzame queries)

[Verwijderd]

Hi (geen 1 april grap ),

Ik heb een paar databasevraagjes. Ik draai een tabel van 700.000 entries..SELECT queries hierop die zonder index draaien worden erg langzaam (denk aan paar seconden..waar ik max 1 seconde wil.. )

1. Query sneller maken
Hoe optimaliseer ik 'SELECT name FROM names WHERE name LIKE '%blabla%' queries.
Het gaat hier om de dubbele procent tekens..wanneer ik 'blabla%' doe gebruikt ie gewoon de name index..maar nu niet (EXPLAIN gedaan..).

2. Count query versnellen
Is een SELECT COUNT(*) sneller dan een normale SELECT * ? Of maakt dit niet uit.. Mijn probleem is dat ik voor het laten zien van de resultaten het totaal aantal results nodig heb. Deze count's duren soms wat lang (paar seconden)..wat de gebruiker laat wachten.. Het beste zou een snelle COUNT(*) zijn en dan een query met LIMIT en OFFSET oid..die per pagina de resultaten snel laat zien.

3. Resultaten tellen
Hoe count ik het totaal van een query waarin ook een GROUP BY en HAVING zit:

code:

SELECT name, order FROM names GROUP BY order HAVING COUNT(order) = 2

Bovenstaande query SELECT alle name's met 2 orders.. maar wat ik wil weten is HOEVEEL dit er zijn. Tot nu is mijn enige oplossing de bovenstaande query uitvoeren en in de aanroepende scripttaal het aantal resultaten opvangen..

Ik hoop op suggesties die alles wat sneller kunnen maken..Het draait nu op een dual Xeon.. en das al langzaam als ie geen indexen kan gebruiken..dus: laat je horen (My)SQL guru's!!

[ Voor 3% gewijzigd door Verwijderd op 01-04-2006 16:05 ]

[megamuch]

Misschien is het slimmer, om even je db model te posten met je gebruikte indexen

[Verwijderd]

1) Het gaat volgens mij niet lukken om hier een index voor te gebruiken.

Een index werkt namelijk op basis van een volgorde waarop gesorteerd kan worden. Als je 'test%' gebruikt zal de sortering blijven werken, aangezien hij weet dat hij alle woorden met als eerste letter a t/m s kan overslaan. Als je echter '%test%' gebruikt, weet hij nooit welke letter in de kolomwaarde met de eerste t van test zal matchen. Daarom zal hij altijd alle kolomwaarden stuk voor stuk moeten doorzoeken.

2) Met een select count(*) zal normaal gesproken altijd sneller moeten zijn, aangezien:
a) er minder resultaten terugkomen, nl. 1 getal ipv de inhoud van de hele tabel
b) als er een index staat op de sleutelwaarde van de kolom, zal hij direct weten hoeveel waarden er in de tabel staan zonder de tabel te benaderen

Wat jij wil is dus niet een hele tabel ophalen en dan skippen de resultaten doorlopen naar het juiste beginresultaat van de huidige pagina, maar direct alleen de juiste records voor de hudige pagina ophalen, en dat is volgens mij prima te doen LIMIT en OFFSET.

Ik neem aan dat je vraag zo beantwoord is.

3)

Bovenstaande query SELECT alle name's met 2 orders..

Uhm ... volgens mij niet, want daarvoor moet je een GROUP op Name doen ipv op Order

Eerlijk gezegd zie ik zo snel niet hoe je dit eleganter kan doen, tenzij MySQL deze ranzige query slikt:

SQL:

SELECT COUNT(name) 
FROM names 
GROUP BY name 
HAVING COUNT(order) = 2

HTH

[Gert]

Wanneer je begint met % worden er geen indexen gebruikt, zover ik weet, da's bij veel database engines zo.

[Verwijderd]

1: Dit kan je niet met een normale index.. misschien kan je iets doen met FULLTEXT matching

2: Een COUNT(*) ophalen is zowiezo sneller, aangezien je alle data moet fetchen om de rowcount te weten, en hiervoor is meer I/O nodig

3: Dit kan je interdaad niet makkelijk met een normale COUNT. Gebruik hier SQL_CALC_FOUND_ROWS voor:

code:

SELECT SQL_CALC_FOUND_ROWS name, order FROM names GROUP BY order HAVING COUNT(order) = 2

[Olaf van der Spek]

Verwijderd schreef op zaterdag 01 april 2006 @ 16:04:
Hoe optimaliseer ik 'SELECT name FROM names WHERE name LIKE '%blabla%' queries.

Is name een uniek veld in deze tabel?

[Verwijderd]

Allen bedankt voor t antwoorden!

MrX: Het kan idd zo zijn dat die Query niet klopt.. maar het idee was hopelijk duidelijk. Ik ga de suggestie van bosmeeuw proberen (SQL_CALC_FOUND_ROWS).

Verder kan ik voor probleem 1 volgens mij geen gebruik maken van FULLTEXT matching aangezien je dan alleen gemachde woorden terugkrijgt?! Een fulltext als:

code:

MATCH name AGAINST ('irkjan', 'enkjan')

matched volgens nooit de namen 'dirkjan' en 'henkjan'??? (om even twee namen te noemen ) Of zijn hier oplossingen voor?

[Verwijderd]

OlafvdSpek: Name kan ik wel uniek maken denk ik jah.. helpt dit?

[Olaf van der Spek]

Verwijderd schreef op zaterdag 01 april 2006 @ 23:10:
OlafvdSpek: Name kan ik wel uniek maken denk ik jah.. helpt dit?

Het hangt ervan af hoe groot de names tabel is en hoeveel orders/name je hebt.
Kun je de describe output voor die tabel niet posten?

Een oplossing zou zijn een aparte tabel te maken alleen voor het name veld. Een table scan op die tabel gaat dan sneller omdat er minder velden zijn.

[JKVA]

Voor je eerste probleem kun je een FULLTEXT index proberen.

Voor je tweede zou je ook nog verder kunnen gaan dan SELECT COUNT(*), bijvoorbeeld SELECT COUNT(tabel_id_kolom). Hoe meer kolommen je opvraagt, hoe trager, is meestal een goeie vuistregel. Een count op één kolom scheelt dan iets. Welke kolom boeit volgens mij niet zo veel, maar ik doe meestal de primary key.

M.b.t. je derde probleem.

SQL:

SELECT name, order FROM names GROUP BY order HAVING COUNT(order) = 2

Zou ik zoiets doen:

SQL:

SELECT COUNT(name)
FROM (
    SELECT name, COUNT(order)
    FROM names
    GROUP BY name
    HAVING COUNT(order) = 2
) AS tmp

Uit de losse pols, dus er kunnen wat typefouten ofzo in staan.

[whoami]

Verwijderd schreef op zaterdag 01 april 2006 @ 16:04:

1. Query sneller maken
Hoe optimaliseer ik 'SELECT name FROM names WHERE name LIKE '%blabla%' queries.
Het gaat hier om de dubbele procent tekens..wanneer ik 'blabla%' doe gebruikt ie gewoon de name index..maar nu niet (EXPLAIN gedaan..).

Als je gebruik maakt van %bliep% criteria, zullen er nooit indexes kunnen gebruikt worden.
Het hangt af van je toepassing bij die %bliep% queries, maar misschien is het handig om eens naar full-text searching te kijken ? (Als MySQL dat ondersteunt).

2. Count query versnellen
Is een SELECT COUNT(*) sneller dan een normale SELECT * ? Of maakt dit niet uit.. Mijn probleem is dat ik voor het laten zien van de resultaten het totaal aantal results nodig heb. Deze count's duren soms wat lang (paar seconden)..wat de gebruiker laat wachten.. Het beste zou een snelle COUNT(*) zijn en dan een query met LIMIT en OFFSET oid..die per pagina de resultaten snel laat zien.

Ik denk dat je ervan kunt uitgaan dat een select count(*) sneller is.... Zowiezo moet er minder data over de lijn gestuurd worden.

3. Resultaten tellen
Hoe count ik het totaal van een query waarin ook een GROUP BY en HAVING zit:

code:

SELECT name, order FROM names GROUP BY order HAVING COUNT(order) = 2

Bovenstaande query SELECT alle name's met 2 orders.. maar wat ik wil weten is HOEVEEL dit er zijn. Tot nu is mijn enige oplossing de bovenstaande query uitvoeren en in de aanroepende scripttaal het aantal resultaten opvangen..

Kijk eens hoe je die GROUP BY precies moet gebruiken, want in jouw voorbeeld gebruik je 'm niet goed.
Ieder veld dat je in je select lijst opneemt, en die geen aggregaat is, moet in de group by komen.
Je zal dus op name en order moeten groeperen in jouw geval.

_{Eh, ff over de ndere reacties heen gekeken.}

[ Voor 3% gewijzigd door whoami op 02-04-2006 11:49 ]

[Olaf van der Spek]

JKVA schreef op zondag 02 april 2006 @ 11:42:
Voor je tweede zou je ook nog verder kunnen gaan dan SELECT COUNT(*)

Een count(*) telt gewoon het aantal rijen en haalt helemaal geen kolommen op. Een count(kolom) is zelfs trager omdat kolom wel opgehaald moet worden (alleen niet NULL fields worden geteld IIRC).

[whoami]

OlafvdSpek schreef op zondag 02 april 2006 @ 11:49:
[...]

Een count(*) telt gewoon het aantal rijen en haalt helemaal geen kolommen op. Een count(kolom) is zelfs trager omdat kolom wel opgehaald moet worden (alleen niet NULL fields worden geteld IIRC).

Je kan wel een count(1) doen

_{Ben je dat trouwens zeker van die NULL fields ? Dat zou wel helemaal dwaas zijn, want een NULL field wil zeggen dat er een row is}

[JKVA]

OlafvdSpek schreef op zondag 02 april 2006 @ 11:49:
[...]

Een count(*) telt gewoon het aantal rijen en haalt helemaal geen kolommen op. Een count(kolom) is zelfs trager omdat kolom wel opgehaald moet worden (alleen niet NULL fields worden geteld IIRC).

Hmm, in mijn ervaring is count(tabel_id) toch sneller dan count(*). Toch maar eens opnieuw naar kijken.

[Olaf van der Spek]

whoami schreef op zondag 02 april 2006 @ 11:50:
Je kan wel een count(1) doen

_{Ben je dat trouwens zeker van die NULL fields ? Dat zou wel helemaal dwaas zijn, want een NULL field wil zeggen dat er een row is}

Als je rows wil tellen, moet je count(*) gebruiken.

code:

mysql> create table a (b int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into a values (null), (1), (2), (null);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select count(*) from a;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.02 sec)

mysql> select count(b) from a;
+----------+
| count(b) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from a;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

mysql> explain select count(b) from a;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql>

[ Voor 71% gewijzigd door Olaf van der Spek op 02-04-2006 12:13 ]

[JKVA]

Ik zeg niet dat mijn methode perfect is (een kolom gebruiken), maar jouw post wil hier verder niet echt veel zeggen naar mijn idee. Ok, nullwaarden worden niet meegenomen en in sommige gevallen zelfs duplicate waarden, maar bij een PK speelt dit niet echt naar mijn idee, aangezien deze altijd not-null, indexed en unique zijn.

Verder kun je qua performance niet veel zeggen met een tabel met 4 records.

[Olaf van der Spek]

JKVA schreef op zondag 02 april 2006 @ 16:54:
Ik zeg niet dat mijn methode perfect is (een kolom gebruiken), maar jouw post wil hier verder niet echt veel zeggen naar mijn idee.

Mijn post was (alleen) een response op whoami's vraag.

[Annie]

JKVA schreef op zondag 02 april 2006 @ 11:53:
[...]


Hmm, in mijn ervaring is count(tabel_id) toch sneller dan count(*). Toch maar eens opnieuw naar kijken.

Als de query optimizer ook maar enigzins intelligent is, dan zou bij een count(*) ook gewoon de meest geschikte index gebruikt moeten worden. En maakt het dus niets uit.

Maar ik moet OlafvdSpek gelijk geven: bij de bepaling van het aantal rows gewoon count(*) gebruiken en niet count(kolom), ook al is het een PK.
Count(*) is gewoon zonder twijfel altijd het totaal aantal rows en is daar ook voor bedoeld; in alle andere gevallen is dat niet meteen duidelijk (zonder kennis van het model of de data) en kan het alleen maar tot vergissingen leiden.