|
Imam slednata tablica:
CREATE TABLE docinfn (cl_id INT UNSIGNED,
doc_id INT UNSIGNED NOT NULL,
word_id INT UNSIGNED NOT NULL,
weight INT UNSIGNED NOT NULL,
tf INT UNSIGNED NOT NULL,
cl0 INT UNSIGNED,
cl1 INT UNSIGNED,
cl2 INT UNSIGNED,
cl3 INT UNSIGNED,
KEY(cl_id), KEY(doc_id),
KEY(word_id), KEY(cl0),
KEY(cl1), KEY(cl2), KEY(cl3));
Izpylniavam slednata zayavka vyrhu taya tablica:
SELECT doc_id, SUM(weight)
FROM docinfn
WHERE (word_id IN (1, 2, 3)) AND (cl0 <> 50) AND (doc_id NOT IN (50, 60, 70))
GROUP BY doc_id
ORDER BY 2 DESC
LIMIT 100;
Kakvi dopylnitelni indexi triabva da pusna, 4e da uskoria neshtata (imam okolo 30 000 000 zapisa)? Ima li na4in da se oprimizira zayavkata?
Probval sym s edin dopylnitelen index KEY(word_id, cl0) i kato izpylnig slednata zayavka:
SELECT doc_id, SUM(weight)
FROM docinfn
WHERE (word_id IN (1, 2, 3)) AND (cl0 <> 50)
GROUP BY doc_id
ORDER BY 2 DESC
LIMIT 100;
Neshtata ne se uskoriha, a az o4akvah da ima goliama razlika. Dopylnitelnite indexi syshto dosta zabaviat INSERT-ite, koeto e i logino.
Iskam da dopylnia, 4e zayavkata moje da se razli4ava malko. Primerno v zayavkata moje da prisystva i cl1, cl2, cl3 ili nito edno da ne prisystva
(vklu4itelno i cl0), t.e. v dopylnenie na gornata zayavka v klauzata WHERE moje da imam neshto ot vida na: + cl1 <> xxx AND cl2 <>zzz
AND cl3<>qqq.
(doc_id NOT IN (50, 60, 70))- poniakoga moje i da ne prisystva v zayavkata. Zayavkata ya generiram dinami4no.
Mersi predvaritelno.
|
MySQL- bavno query
