MySQL Forums
Forum List  »  German

JOIN n:m Tabelle viele Rows
Posted by: Matthias Pieroth
Date: December 11, 2010 01:13PM

Hallo NG,

ich habe 2 Tabellen die ich mit einem JOIN abfrage:

CREATE TABLE question (
qid int(11) AUTO_INCREMENT NOT NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
idmember int(11) NOT NULL,
creationdate datetime NOT NULL,
views int(11) NOT NULL DEFAULT '0',
answers int(11) NOT NULL DEFAULT '0',
sendmail tinyint(4) NOT NULL DEFAULT '1',
so_qid int(11) NULL,
providertype int(11) NOT NULL DEFAULT '0',
lastvisit datetime NULL,
PRIMARY KEY(qid)
)
;
ALTER TABLE question
ADD CONSTRAINT idx_u_so_qid
UNIQUE (so_qid)
;

CREATE TABLE question_tag (
qid int(11) NOT NULL,
tagid int(11) NOT NULL
)
;
ALTER TABLE question_tag
ADD CONSTRAINT u_idx_q_t
UNIQUE (qid, tagid)
;

Hier der JOIN:

select q.* from question q, question_tag qt where q.qid=qt.qid
and qt.tagid=4711 // ist variabel
order by q.lastvisit desc
limit 15

Explain extended sieht so aus:


id select_type table type possible_keys key key_len ref rows filtered Extra
----- -------------- -------- ------- ----------------- ------- ---------- ----------------- ------- ----------- --------------------------------------------
1 SIMPLE qt ref u_idx_q_t,idx_tag idx_tag 4 const 1 100 Using index; Using temporary; Using filesort
1 SIMPLE q eq_ref PRIMARY PRIMARY 4 cg_db.qt.qid 1 100

select `cg_db`.`q`.`qid` AS `qid`,`cg_db`.`q`.`title` AS `title`,`cg_db`.`q`.`description` AS `description`,`cg_db`.`q`.`idmember` AS `idmember`,`cg_db`.`q`.`creationdate` AS `creationdate`,`cg_db`.`q`.`views` AS `views`,`cg_db`.`q`.`answers` AS `answers`,`cg_db`.`q`.`sendmail` AS `sendmail`,`cg_db`.`q`.`so_qid` AS `so_qid`,`cg_db`.`q`.`providertype` AS `providertype`,`cg_db`.`q`.`lastvisit` AS `lastvisit` from `cg_db`.`question` `q` join `cg_db`.`question_tag` `qt` where ((`cg_db`.`qt`.`tagid` = 457) and (`cg_db`.`q`.`qid` = `cg_db`.`qt`.`qid`)) order by `cg_db`.`q`.`lastvisit` desc limit 15

Die Felder q.lastvisit und qt.tagid sind indexiert.

Das Problem ist die performance. Wenn die Einschränkung auf qt.tagid wenige Datensätze zurückliefert dauert die query ewig (nach 3 Minuten habe ich abgebrochen). Wenn es für qt.tagid sehr viele gibt z.B. 3000, dann ist die Query in 60ms durch.

Mengengefüge: Query-Tabelle 1 Mio rows, query_tag ca. 2.8 Mio rows.

Hat jemand eine Idee was ich opimieren könnte?

Danke und Grüße
Matthias

Options: ReplyQuote


Subject
Views
Written By
Posted
JOIN n:m Tabelle viele Rows
4155
December 11, 2010 01:13PM
1509
December 12, 2010 05:23AM
2126
December 13, 2010 06:17AM
1165
December 13, 2010 08:10AM
858
December 14, 2010 07:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.