MySQL- bavno query

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.

po moeto skromno mnenie i to ne mnogo very high profesional
predlagam ti tova :
create temp_table SELECT * FROM docinfn WHERE
(word_id IN (1,2,3)//tova po-dobre taka (word_id=1 or word_id=2 or word_id=3)
) AND (cl0 <> 50)
AND (doc_id NOT IN (50, 60, 70)) // kato gornoto

i posle vtoro query :

SELECT doc_id, SUM(weight) FROM temp_table
GROUP BY doc_id
ORDER BY 2 DESC
LIMIT 100;

E tuk ni6to niama :[линк]

drop temp_table of course :-)))
kato temp table moze da ti e slu4aino generiran string ako ima6 konkurentni zaiavki online

E tuk ni6to niama :[линк]

за да не се кривиш със случайни имена и да се чудиш зали няма да съвпаднат.

CREATE TEMPORARY TABLE xx ...;

salle.online.bg/sig

Отрицателната логика не позволява използването на индекси. Напр.
AND (cl0 <> 50)
означава че никога няма да използва индекс в/у цл0, а
AND (doc_id NOT IN (50, 60, 70)
не позволява да се използва индекс в/у док_ид

Mersi za otgovora. Zna4i probvah zayavkite deto si gi dal i rezultatyt e slednia. Moite zayavki, koito sa ekvivalentni na tvoite se izpylniavat za okolo 3-4 pati po-byrzo ot tvoite.
Mnogo vreme se gubi pri syzdavaneto na vremennata tablica. SELECT-a vyv vremennata tablica otnema 1/2 ot vremeto za izpylnenie na moyata zayavka.

А ти временната таблица без индекси ли я създаваш?

salle.online.bg/sig

Обърни внимание на това, което baj l\bo ти е написал и помисли дали няма начин да обърнеш логиката на "положителна" ако разпределението на стойностите ти го позволява.
т.е. или да изброиш останалите или да го направиш със JOIN

В много случаи примерно е възможно
a <> now() да се замени със a < now() и това да ускопри значително нещата праста защото първото не може да използва индекс а второто може.

Също така може да се окаже много по-бързо да използваш UNION за да разбиеш заявката на парчета с различни WHERE клаузи.

Изчети също какво означават резултатите от EXPLAIN - това е най-доброто другарче при оптимизиране.

Между другото защо нямаш Primary Key ?

salle.online.bg/sig

Da bez indexi, no tova ne e ot zna4enie poneje parvoto query, t.e.
create temp_table SELECT * FROM docinfn WHERE
(word_id IN (1,2,3)) AND (cl0 <> 50)
AND (doc_id NOT IN (50, 60, 70))

mi otnama mnogo po-ve4e vreme otkolkoto moeto.
Daje i kato mahna 4asta AND (cl0 <> 50)
AND (doc_id NOT IN (50, 60, 70)).

Ako pusna indexi na vremennata tablica INSERT-ite shte se zabaviat mnogo a SELECTA shte se uskori i v krayna smetka sashtata rabota shte se polu4i.

Я кажи колко е:

SELECT COUNT(*) FROM docinfn WHERE word_id IN (1,2,3);

word_id е индексирано та това трябва да е бързичко.

А също и:

SELECT COUNT(*), COUNT(DISTINCT word_in), COUNT(DISTINCT c10), COUNT(DISTINCT doc_id) FROM docinfn;

Това обаче ще е доста по-бавно

salle.online.bg/sig

SELECT COUNT(*) FROM docinfn WHERE word_id IN (1,2,3);
+----------+
| COUNT(*) |
+----------+
| 102499 |
+----------+
1 row in set (0.34 sec)


SELECT COUNT(*), COUNT(DISTINCT word_id), COUNT(DISTINCT cl0), COUNT(DISTINCT doc_id) FROM docinfn;
+----------+-------------------------+---------------------+------------------------+
| COUNT(*) | COUNT(DISTINCT word_id) | COUNT(DISTINCT cl0) | COUNT(DISTINCT doc_id) |
+----------+-------------------------+---------------------+------------------------+
| 26481240 | 802396 | 211999 | 200000 |
+----------+-------------------------+---------------------+------------------------+
1 row in set (18 min 45.17 sec)

EXPLAIN SELECT doc_id, SUM(weight)
-> FROM docinfn
-> WHERE (word_id IN (541, 646, 345, 2446, 3430, 4430, 5451, 245, 245, 349, 1420, 2435, 1434, 2445, 3456, 4467, 547,6749))
-> GROUP BY doc_id
-> ORDER BY 2 DESC
-> LIMIT 100;
+---------+-------+---------------+---------+---------+------+-------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+-------+---------------+---------+---------+------+-------+---------------------------------------------+
| docinfn | range | word_id | word_id | 4 | NULL | 78168 | where used; Using temporary; Using filesort |
+---------+-------+---------------+---------+---------+------+-------+---------------------------------------------+
1 row in set (0.20 sec)


Kato pusna Primary Key(word_id, doc_id) i tyrseneto slabo se vloshava a za INSERT-ite znaesh kak vliae Primary Key.

Ами при това разпределение мисля, че решението (без преструктуриране) е еднозначно

CREATE TEMPORARY TABLE temp SELECT * FROM docinfn WHERE word_id IN (1,2,3);

Виж колко голяма става тази таблица като байтове защата ако можеш да използваш TYPE=HEAP още по-добре.

SELECT FROM temp ... всичко останало.

при това положение ако нямаш други заявки в оригиналната таблица оставяш САМО този индекс


Вместо SELECT ... можеш да го разбиеш на още стъпки примерно:

DELETE FROM temp WHERE cl0 < 50;
DELETE FROM temp WHERE cl0 > 50;

това обаче ще е по-бързо ако създаваш временната таблица с индекс и тя НЕ Е HEAP (защото HEAP преди 4.1 поддържат само
HASH индекси а те не стават за >, <

SELECT ...


Обаче генералното решение е да разбиеш таблицата вертикално на по-малки таблици защото тогава задачката ти ще бъде за JOIN и индексите ще работят по-добре .... може би разбира се :)

Разбира се ще ти се усложнят нещата при всяко писане в таблиците.

Честно казано не очаквах чак 26 милиона реда
А колко е като обем това нещо? Може да се окаже, че има какво да се пипне по настройките.

Каква ти е версията между другото?

salle.online.bg/sig

И вземи пиши на кирилица, че се вбесявам.

Ще взема да го засека някой ден но един и същ текст на кирилица се чете около 30-40 пъти по бързо ... а времето е пари все пак.

salle.online.bg/sig

mysql Ver 11.16 Distrib 3.23.49, for unknown-linux-gnu (alphaev6)

-rw-rw---- 1 mysql mysql 979805880 Mar 11 14:02 docinfn.MYD
-rw-rw---- 1 mysql mysql 1646059520 Mar 11 14:36 docinfn.MYI
-rw-rw---- 1 mysql mysql 8750 Mar 11 14:00 docinfn.frm

Не съм писал на кирилица от 2 години насам. Много рядко (веднъж на месец а може и по-малко) ми се налага да говоря български.

тъкмо няма да забравяш как се пише на кирилица..))

[линк]

[линк]