[mysql] tworzenie indeksów Opcje

[Zick4]

Mam takie 2 tabelki. announcement:
http://www.fotosik.pl/pokaz_obrazek/pelny/...af62312b98.html

i category, który ma utworzone indeksy przez mojego poprzednika
http://www.fotosik.pl/pokaz_obrazek/pelny/...71fa72ecb5.html

Chciałbym trochę przyspieszyć te 2 zapytania przez dodanie indesy na tabeli announcement, no chyba że cateogory wymaga poprawy indeksów
http://www.fotosik.pl/pokaz_obrazek/pelny/...95741012d3.html
http://www.fotosik.pl/pokaz_obrazek/pelny/...3dca3afa07.html

Ten post edytował Zick4 12.12.2009, 16:58:17

[Mchl]

Dwa ostatnie linki nie działają.

Tak czy inaczej
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

[Zick4]

Już poprawione, znam polecenie explain i dla wiem, że powinno się zakładać indeksy tam, gdzie pojawiają się w klauzuli where, group by i order by, ale moje próby spełzają na niczym i nie przyspieszają się te zapytania

Ten post edytował Zick4 12.12.2009, 17:00:37

[Mchl]

No a co mówi explain? I może wklej tutaj też zapytanie, bo jakoś do tych obrazków się dostać nie mogę.

[Mchl]

No to na początek indeksy na:
announcement.category_id
announcement.city_id
announcement.status - jakie wartości są w tej kolumnie? być może nie trzeba zakłądać na wszytkie 9 znaków, tylko na kilka pierwszych

P.S. No i nie wiem co z tym fotosikiem. Dwa pierwsze linki mi wchodzą, a dwa pozostałe ani dudu

P.P.S.

A tak w ogóle, to te zapytania powinny dać Ci te same wyniki:

Kod

SELECT COUNT(DISTINCT a.id) AS num_results FROM announcement a LEFT JOIN category c ON a.category_id = c.id LEFT JOIN category c3 ON c.parent_id = c3.id WHERE a.status = 'S' AND (c.parent_id = 'S' OR c3.parent_id = 'S') GROUP BY a.id;

SELECT DISTINCT a4.id FROM announcement a4 LEFT JOIN category c4 ON  a4.category_id = c4.id LEFT JOIN  category c6 ON c4.parent_id = c6.id WHERE a4.status = 'S' AND  (c4.parent_id = 'S' OR c6.parent_id = 'S') ORDER BY a4.highlight,  a4.price DESC LIMIT 10;

Ten post edytował Mchl 12.12.2009, 23:37:28

[Zick4]

No to na początek indeksy na:
announcement.category_id
announcement.city_id
announcement.status - jakie wartości są w tej kolumnie? być może nie trzeba zakłądać na wszytkie 9 znaków, tylko na kilka pierwszych

ale 1 wielokolumnowy czy 3 osobne? Utworzyłem i tak i tak. W kolumnie status są wartości: waiting, publish, unpublish. Wyniki są tutaj:
przed i po

A tak w ogóle, to te zapytania powinny dać Ci te same wyniki:

To zapytanie generuje mi doctrine. Nie mam wpływu na kształt zapytania

[Mchl]

Kolumnę status możesz zamienić na ENUM('waiting', 'publish', 'unpublish')
Zajmie mniej miejsca a i indeks powinien być szyybszy.

Chodziło mi o indeksy jednokolumnowe na razie. Niestety jak zwykle nie mogę się dostać do fotosika.

[Zick4]

Kolumnę status możesz zamienić na ENUM('waiting', 'publish', 'unpublish')
Zajmie mniej miejsca a i indeks powinien być szyybszy.

Zrobione

Niestety jak zwykle nie mogę się dostać do fotosik

Nic nie straciłeś. Przed było, że żadnego sensownego indeksy nie odnalazł, a wierszy dał 511157. Natomiast po ustawieniu indeksu na status:

[SQL] pobierz, plaintext 
EXPLAIN
SELECT COUNT(DISTINCT a.id) AS num_results FROM announcement a LEFT JOIN category c ON a.category_id = c.id LEFT JOIN city c2 ON a.city_id = c2.id LEFT JOIN province p ON c2.province_id = p.id LEFT JOIN announcement_has_attribute_value a3 ON a.id = a3.announcement_id LEFT JOIN attribute_value a2 ON a2.id = a3.attribute_value_id LEFT JOIN category c3 ON c.parent_id = c3.id WHERE a.STATUS = 'publish' AND (c.parent_id = '527' OR c3.parent_id = '527') GROUP BY a.id;
[SQL] pobierz, plaintext 

+----+--------------+-------+---------+----------------+------------+----------+--------------------------------+----------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------+---------+----------------+------------+----------+--------------------------------+----------+--------------------------------+
| 1 | SIMPLE | a | ref | status | status | 2 | const | 511042 | Using where; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.a.category_id | 1 | |
| 1 | SIMPLE | c2 | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.a.city_id | 1 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.c2.province_id | 1 | Using index |
| 1 | SIMPLE | a3 | ref | PRIMARY | PRIMARY | 4 | xxxx.a.id | 1 | Using index |
| 1 | SIMPLE | a2 | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.a3.attribute_value_id | 1 | Using index |
| 1 | SIMPLE | c3 | eq_ref | PRIMARY | PRIMARY | 4 | xxxx.c.parent_id | 1 | Using where |
+----+-------------+-------+---------+-----------------+------------+----------+--------------------------------+----------+---------------------------------+

Ten post edytował Zick4 14.12.2009, 01:38:58

[phpion]

Kolumnę status możesz zamienić na ENUM('waiting', 'publish', 'unpublish')
Zajmie mniej miejsca a i indeks powinien być szyybszy.

Brednie i głupoty! Zakładanie indeksów na kolumny, w których jest niewielkie zróżnicowanie wartości (tu masz 3) to jest zły pomysł. Taki indeks nic nie da, a tylko zabierze miejsce i spowolni aktualizację danych (konieczność aktualizacji indeksu). Wspomniany indeks należy wywalić w diabły bo nie przyniesie żadnych korzyści.

[Zick4]

To jak przyspieszyć to zapytanie? Wykonuje się ze 10 s. ;( a mam takie 2 zapytania, które muszę zoptymalizować.

[Mchl]

Brednie i głupoty! Zakładanie indeksów na kolumny, w których jest niewielkie zróżnicowanie wartości (tu masz 3) to jest zły pomysł. Taki indeks nic nie da, a tylko zabierze miejsce i spowolni aktualizację danych (konieczność aktualizacji indeksu). Wspomniany indeks należy wywalić w diabły bo nie przyniesie żadnych korzyści.

Rację masz, ale nie do końca. Oprócz kryterium mocy indeksu, ważny jest też rozkład wartości w tabeli, oraz to, które wartości nas najczęściej interesują.
Wyobraź sobie tabelę z kolumną ENUM('interesujace','nieinteresujace'), gdzie tylko 10% wierszy ma wartość 'interesujace', ale za to (zgodnie z nazwą) są to wiersze które nas najczęściej interesują. Taki indeks też wywalisz?

Niestety u Zick4 najwyraźniej jest akurat odwrotnie. Większość wierszy ma status 'publish'. Tak więc indeks na kolumnie status nic nie daje w tym zapytaniu (ale powinien się wykazać przy wyszukiwaniu status = 'waiting' lub 'unpublish').

[Zick4]

Niestety u Zick4 najwyraźniej jest akurat odwrotnie. Większość wierszy ma status 'publish'. Tak więc indeks na kolumnie status nic nie daje w tym zapytaniu (ale powinien się wykazać przy wyszukiwaniu status = 'waiting' lub 'unpublish').

Niestety jednak potrzebuję korzystać ze statusu 'publish'. Widzę, że nikt nie ma pomysłu jak to przyspieszyć

[Mchl]

Trudno powiedzieć coś na podstawie samego zapytania i nie znając struktury ani do czego to ma służyć. Najmocniej Cię opóźnia ten filesort w pierwszej tabeli.

http://www.mysqlperformanceblog.com/2009/0...-mean-in-mysql/

Co ma robić to zapytanie (logicznie, bo że zliczyć określone rekordy to widzę )? Może jest jeszcze jakiś warunek, który można narzucić na tabelę announcement?

[Zick4]

[SQL] pobierz, plaintext 
EXPLAIN 
SELECT a.id AS a__id, a.category_id AS a__category_id, a.city_id AS a__city_id, a.title AS a__title, a.description AS a__description, a.price AS a__price, a.thumbnail_photo AS a__thumbnail_photo, a.create_time AS a__create_time, a.url AS a__url, c.id AS c__id, c.name AS c__name
FROM announcement a
LEFT JOIN category c ON a.category_id = c.id
LEFT JOIN category c2 ON c.parent_id = c2.id
WHERE a.STATUS = 'publish'
AND (
c.parent_id = '527'
OR c2.parent_id = '527'
)
ORDER BY a.highlight, a.price DESC
LIMIT 10 
[SQL] pobierz, plaintext 

+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+
| 1 | SIMPLE | a | ref | status | status | 2 | const | 511042 | Using where; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | argetto.a.category_id | 1 | |
| 1 | SIMPLE | c2 | eq_ref | PRIMARY | PRIMARY | 4 | argetto.c.parent_id | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+

[SQL] pobierz, plaintext 
EXPLAIN SELECT COUNT( DISTINCT a.id ) AS num_results
FROM announcement a
LEFT JOIN category c ON a.category_id = c.id
LEFT JOIN category c2 ON c.parent_id = c2.id
WHERE a.STATUS = 'publish'
AND (
c.parent_id = '527'
OR c2.parent_id = '527'
)
GROUP BY a.id
[SQL] pobierz, plaintext 

+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+
| 1 | SIMPLE | a | ref | status | status | 2 | const | 511042 | Using where; Using filesort |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | argetto.a.category_id | 1 | |
| 1 | SIMPLE | c2 | eq_ref | PRIMARY | PRIMARY | 4 | argetto.c.parent_id | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-----------------------------+

Powyższe zapytania służą do paginacji wyników na stronie przez Doctrine. Generalnie ogłoszenie należy do kategorii w relacji 1:n, kategorie mogą mieć inne pod kategorie, czyli między nimi jest również relacja 1:n. I teraz wyświetlając wyniki dla kategorii np. praca, która ma swoje id muszę wyświetlić ogłoszenia z podkategorii. I tu jest pies pogrzebany, bo wyświetlanie ogłoszeń tylko dla rodzica (bez patrzenia na dzieci) działa potężnie szybciej, ale średnio dobrze:

[SQL] pobierz, plaintext 
EXPLAIN  SELECT COUNT(  DISTINCT a.id )  AS num_results
FROM announcement a
LEFT  JOIN category c ON a.category_id = c.id
WHERE a.STATUS =  'publish'
AND (c.parent_id =  '527')
GROUP  BY a.id
[SQL] pobierz, plaintext 

+----+-------------+-------+------+------------------------------------------------+---------------+---------+--------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------------------------+---------------+---------+--------------------+------+----------------------------------------------+
| 1 | SIMPLE | c | ref | PRIMARY,idx_position_order_by,position | position | 5 | const | 17 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | category_id,category_id_2,status,category_id_3 | category_id_3 | 6 | argetto.c.id,const | 970 | Using where |
+----+-------------+-------+------+------------------------------------------------+---------------+---------+--------------------+------+----------------------------------------------+

Oraz:

[SQL] pobierz, plaintext 
EXPLAIN
SELECT a.id AS a__id, a.category_id AS a__category_id, a.city_id AS a__city_id, a.title AS a__title, a.description AS a__description, a.price AS a__price, a.thumbnail_photo AS a__thumbnail_photo, a.create_time AS a__create_time, a.url AS a__url, c.id AS c__id, c.name AS c__name
FROM announcement a
LEFT JOIN category c ON a.category_id = c.id
WHERE a.STATUS = 'publish'
AND (
c.parent_id = '527'
)
ORDER BY a.highlight, a.price DESC
LIMIT 10;
[SQL] pobierz, plaintext 

+----+-------------+-------+--------+------------------------------------------------+---------------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------------------+---------------+---------+---------------------+------+----------------------------------------------+
| 1 | SIMPLE | c | ref | PRIMARY,idx_position_order_by,position | position | 5 | const | 17 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c2 | eq_ref | PRIMARY | PRIMARY | 4 | argetto.c.parent_id | 1 | Using index |
| 1 | SIMPLE | a | ref | category_id,category_id_2,status,category_id_3 | category_id_3 | 6 | argetto.c.id,const | 970 | Using where |
+----+-------------+-------+--------+------------------------------------------------+---------------+---------+---------------------+------+----------------------------------------------+

Zastanawia mnie jak to jest zrobione w większych serwisach jak allegero :/