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

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

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

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

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:

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


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

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.

Zrobione

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 

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.

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

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').

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

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?

[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 :/