[Query vraag]Query met koppeltabel erg traag

[van.der.schulting]

Ik heb de volgende database

Ik heb de volgende tabellen
Advertisements (50.000 records)

code:

+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name                | varchar(255) | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+

Brands (25 records)

code:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Join tabel (Advertisements has and belongs to many Brands) (iets meer dan 50.000 records)

code:

+------------------+---------+------+-----+---------+-------+
| Field            | Type    | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| advertisement_id | int(11) | YES  | MUL | NULL    |       |
| brand_id         | int(11) | YES  | MUL | NULL    |       |
+------------------+---------+------+-----+---------+-------+

De meeste advertenties hebben 1 brand. Een enkele advertentie heeft 2 brands

De server is MySQL 5.1 met innoDB

Deze query is prima te doen (Zoeken hoeveel brands horen bij advertentie 1):

code:

Load (0.8ms)  SELECT count(*) FROM `brands` 
INNER JOIN `advertisements_brands` ON `brands`.`id` = `advertisements_brands`.`brand_id` 
WHERE `advertisements_brands`.`advertisement_id` = 1

Deze query is super traag (Zoeken hoeveel advertenties horen bij brand 1):

code:

   (195.9ms)  SELECT COUNT(*) FROM `advertisements` 
INNER JOIN `advertisements_brands` ON `advertisements`.`id` = `advertisements_brands`.`advertisement_id` 
WHERE `advertisements_brands`.`brand_id` = 1

M.a.w. alle brands bij een advertentie zoeken gaat supersnel. Alle advertenties bij een brand zoeken duurt een eeuwigheid.

Volgens mij is wat ik wil een heel simpel probleem. Ik wil een index op brands die per brand alle bijbehorende advertenties bijhoud.
Ik dacht het op te lossen met de volgende index:

code:

CREATE INDEX index_advertisements_brands_on_brand_id_and_advertisement_id 
ON advertisements_brands(brand_id, advertisement_id)

Ik heb ook een unique index geprobeerdf en precies de omgekeerde index (advertisement_id, brand_id), maar geen van alle werkt.

Ik heb niet zo heel veel kennis van indexes en weet niet precies waar ik het zoeken moet. Mijn gevoel zegt dat ik gewoon de verkeerde index aan het definieren ben. Wie kan mij uitleggen wat ik fout doe?

edit:

of is dit wel de goede index en moet ik even geduld hebben om de server zijn index te laten opbouwen voordat het effect heeft?

[ Voor 33% gewijzigd door van.der.schulting op 22-10-2011 01:27 ]

[GlowMouse]

Wat je fout doet:
- je beschrijving van je tabellen is te vraag 'koppeltabel' ipv 'advertisements_brands'; er is SHOW CREATE TABLE
- je queries geef je onleesbaar weer
- je geeft geen output van EXPLAIN

Waar is die join in die tweede query voor nodig?

edit:
dit is leesbaar:

code:

SELECT COUNT(*)
FROM advertisements a
INNER JOIN advertisements_brands ab ON a.id=ab.advertisement_id
WHERE ab.brand_id = 1

[ Voor 25% gewijzigd door GlowMouse op 22-10-2011 01:25 ]

[van.der.schulting]

Niemand die het antwoord weet of me een schop in de goede richting kan geven?

[cariolive23]

van.der.schulting schreef op zaterdag 22 oktober 2011 @ 20:27:
Niemand die het antwoord weet of me een schop in de goede richting kan geven?

Dat kun je zelf doen door te beginnen met het resultaat van EXPLAIN hier neer te zetten. Zonder EXPLAIN hoef je zelfs niet te denken aan het optimaliseren van een query, je hebt dan geen idee wat er aan de hand is.

[Voutloos]

Voor beide queries heb je de join niet eens nodig, enkel de koppeltabel volstaat al. Vervolgens EXPLAIN er bij en de goede indexjes leggen.