[MySQL] Query performance probleem

[Olaf van der Spek]

In mijn forum heb ik een optie om posts te zoeken op naam van poster. Omdat mijn forum zowel gasten als vaste gebruikers ondersteund, gebruik ik de volgende query. Het probleem is echter dat deze query ongeveer 6 uur kost.
1678 guests, 289 users en 9382 messages.

code:

EXPLAIN
SELECT xf_messages . *
FROM xf_messages, xf_guests, xf_users
WHERE instr( lcase( xf_guests.name ) , lcase( "olaf" ) ) AND xf_messages.aid = xf_guests.aid
    OR instr( lcase( xf_users.name ) , lcase( "olaf" ) ) AND xf_messages.uid = xf_users.uid
ORDER BY ctime DESC  
;
+-------------+------+---------------+------+---------+------+------+---------------------------------+
| table       | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+-------------+------+---------------+------+---------+------+------+---------------------------------+
| xf_users    | ALL  | PRIMARY       | NULL |    NULL | NULL |  289 | Using temporary; Using filesort |
| xf_messages | ALL  | aid,uid       | NULL |    NULL | NULL | 9382 |                                 |
| xf_guests   | ALL  | PRIMARY       | NULL |    NULL | NULL | 1678 | where used                      |
+-------------+------+---------------+------+---------+------+------+---------------------------------+

MySQL voert waarschijnlijk een full join van de drie tables uit en dat is natuurlijk niet de bedoeling. Union zou waarschijnlijk uitkomst bieden, maar is niet beschikbaar in versie 3.
Is dit binnen een query op te lossen of kan ik er beter twee of drie queries van maken?

[whoami]

6 uur ?

Tuurlijk gaat die query traag, je selecteert uit 3 tabellen, zonder dat je die tables joined. Als die tabellen ook geen relatie hebben met elkaar, kan je ze natuurlijk niet joinen. Dan zal je idd met een UNION aan de slag moeten. Aangezien je dat niet kan, maak je er best verschillende queries van.

Daarnaast kan je ook best indexen op die tabel leggen op de velden waarop je zoekt.

[Banpei]

Heb je wel indexes op tabellen gezet?

_{En daarnaast zie ik gelijk al dat je continu een instr op (var)char doet. Dat is erg traag.}

Edit: wat is de performance als je de instr. uit de query haalt?

Daarnaast maak je geen joins met een where. Als je dit doet gaat MySQL alle records uit alle tabellen met elkaar matchen en krijg je een cartegisch product. Daarna gaat MySQL in de WHERE uitzoeken welke records je wilt hebben. Probeer het eens met een INNER/LEFT/RIGHT JOIN op tabel nivo: dat zal ook al een hoop schelen.

[ Voor 58% gewijzigd door Banpei op 24-09-2003 16:08 ]

[Olaf van der Spek]

Banpei schreef op 24 September 2003 @ 16:02:
Heb je wel indexes op tabellen gezet?

_{En daarnaast zie ik gelijk al dat je continu een instr op (var)char doet. Dat is erg traag.}

Edit: wat is de performance als je de instr. uit de query haalt?

code:

EXPLAIN
SELECT xf_messages . *
FROM xf_messages, xf_guests, xf_users
WHERE xf_messages.aid = xf_guests.aid AND xf_guests.name = "olaf"
    OR xf_messages.uid = xf_users.uid AND xf_users.name = "olaf"
ORDER BY ctime DESC  
;
+-------------+------+---------------+------+---------+------+------+---------------------------------+
| table       | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+-------------+------+---------------+------+---------+------+------+---------------------------------+
| xf_users    | ALL  | PRIMARY,name  | NULL |    NULL | NULL |  289 | Using temporary; Using filesort |
| xf_messages | ALL  | aid,uid       | NULL |    NULL | NULL | 9382 |                                 |
| xf_guests   | ALL  | PRIMARY,name  | NULL |    NULL | NULL | 1678 | where used                      |
+-------------+------+---------------+------+---------+------+------+---------------------------------+

Indexes zijn niet bruikbaar in combinatie met instr. Instr heb ik gebruikt omdat ik ook "Olaf van der Spek" wil vinden als ik op "van" zoek.
Zonder instr is de explain output hetzelfde en de performance waarschijnlijk ook.

Daarnaast maak je geen joins met een where. Als je dit doet gaat MySQL alle records uit alle tabellen met elkaar matchen en krijg je een cartegisch product. Daarna gaat MySQL in de WHERE uitzoeken welke records je wilt hebben. Probeer het eens met een INNER/LEFT/RIGHT JOIN op tabel nivo: dat zal ook al een hoop schelen.

code:

EXPLAIN
SELECT xf_messages . *
FROM xf_messages, xf_users
WHERE instr( lcase( xf_users.name ) , lcase( "olaf" ) ) AND xf_messages.uid = xf_users.uid
ORDER BY ctime DESC  
;
+-------------+--------+---------------+---------+---------+-----------------+------+----------------+
| table       | type   | possible_keys | key     | key_len | ref             | rows | Extra          |
+-------------+--------+---------------+---------+---------+-----------------+------+----------------+
| xf_messages | ALL    | uid           | NULL    |    NULL | NULL            | 9382 | Using filesort |
| xf_users    | eq_ref | PRIMARY       | PRIMARY |       4 | xf_messages.uid |    1 | where used     |
+-------------+--------+---------------+---------+---------+-----------------+------+----------------+

Volgens mij voert MySQL hier toch een slimme join uit, anders zou het 9382 * 289 zijn.

[ Voor 38% gewijzigd door Olaf van der Spek op 24-09-2003 16:21 ]

[whoami]

Een join kan je perfect in een WHERE clause kwijt.
Alleen join je niet goed.

Waarom heb je trouwens die members en die guests in 2 aparte tabellen gezet? Dat zorgt voor jouw grootste probleem. Nu weet je -denk ik- niet 100% zeker of een post door een member of door een guest gedaan is, aangezien er best een member kan zijn met id x, en een guest datzelfde id kan hebben.
Je moet nu dus allerhande truukjes gaan uitvoeren om dat uit te vogelen.

Je had ze beter in 1 tabel gezet, en dan een extra veldje in die tabel opgenomen waarmee je aangeeft of het over een member of een guest gaat.
Dan worden je queries ook meteen heel wat eenvoudiger.

[ Voor 80% gewijzigd door whoami op 24-09-2003 16:26 ]

[Olaf van der Spek]

whoami schreef op 24 September 2003 @ 16:24:
Een join kan je perfect in een WHERE clause kwijt.
Alleen join je niet goed.

De output is volgens mij wel goed, dus is het eigenlijk de fout van de MySQL optimiser.

Waarom heb je trouwens die members en die guests in 2 aparte tabellen gezet?

In een grijs verleden zonder ervaring met SQL heb ik blijkbaar een designfout gemaakt. Waarschijnlijk wilde ik slechts een join per message doen en niet twee.

Dat zorgt voor jouw grootste probleem. Nu weet je -denk ik- niet 100% zeker of een post door een member of door een guest gedaan is, aangezien er best een member kan zijn met id x, en een guest datzelfde id kan hebben.
Je moet nu dus allerhande truukjes gaan uitvoeren om dat uit te vogelen.

Als xf_messages.aid niet nul is, is het van een guest en als xf_messages.uid niet nul is, is het van een user. Dus dat gaat wel goed.

Je had ze beter in 1 tabel gezet, en dan een extra veldje in die tabel opgenomen waarmee je aangeeft of het over een member of een guest gaat.
Dan worden je queries ook meteen heel wat eenvoudiger.

Dat is waar.

[whoami]

OlafvdSpek schreef op 24 September 2003 @ 16:46:
[...]

De output is volgens mij wel goed, dus is het eigenlijk de fout van de MySQL optimiser.

Als je een query hebt, die zo iets simpels moet doen, en 6 uur duurt, dan mag je er bijna prat op gaan dat er iets fout is met je query.

Als xf_messages.aid niet nul is, is het van een guest en als xf_messages.uid niet nul is, is het van een user. Dus dat gaat wel goed.

[whoami]

Gebruik eens haakjes in je WHERE clausule trouwens.

[Olaf van der Spek]

whoami schreef op 24 september 2003 @ 19:11:
Gebruik eens haakjes in je WHERE clausule trouwens.

Waarom? And heeft hogere prioriteit dan or, dus haakjes zijn niet nodig.

[whoami]

In ieder geval denk ik toch dat je je WHERE het best zo schrijft:

code:

WHERE xf_messages.aid = guests.aid
AND xf_messages.uid = users.uid
AND
( 
  instr(guests.name, "aname") OR
  instr(users.name, "aname")
)

[Olaf van der Spek]

whoami schreef op 24 September 2003 @ 19:20:
In ieder geval denk ik toch dat je je WHERE het best zo schrijft:

code:

WHERE xf_messages.aid = guests.aid
AND xf_messages.uid = users.uid
AND
( 
  instr(guests.name, "aname") OR
  instr(users.name, "aname")
)

Maar of aid of uid is nul, dus die query levert altijd een lege set op.

[whoami]

Dan doe je een outer join he:

code:

FROM xf_messages
LEFT JOIN guests ON xf_messages.aid = guests.aid
LEFT JOIN users ON xf_messages.uid = users.uid
WHERE 
(
  instr( ....... ) OR
  instr( .....)
)

[Olaf van der Spek]

Dank u: 1407 rows in set (0.17 sec)

[whoami]

Snap je ook wat het probleem was?

Je join deed je niet goed. Je ging altijd maar 2 van de 3 tabellen gaan joinen, en dus werd er met die overblijvende tabel een cartesiaans product gemaakt.

[Olaf van der Spek]

whoami schreef op 24 September 2003 @ 19:42:
Snap je ook wat het probleem was?

Je join deed je niet goed. Je ging altijd maar 2 van de 3 tabellen gaan joinen, en dus werd er met die overblijvende tabel een cartesiaans product gemaakt.

Ja, dat snap ik, maar ik ging er vanuit dat de optimiser zou zien dat dat niet nodig was en het dus ook niet zou uitvoeren.

[whoami]

Het DBMS voert uit wat jij hem opdraagt.
Het enige wat de optimizer gaat doen is het beste executie-plan voor die query gaan bepalen. Die gaat zelf niet aan je query gaan klooien ofzo hoor.