VIEWS do zwalczenia indexu z malym cardinality lub inny spos

0

Jest tabela tematow na forum. Jedno z jej pul to FORUM_ID na ktorym jest index.
Problem jest nastepujacy, jesli mamy powiedzmy 2 miliony tematow i tylko 20 roznych for to "moc" (cardinality) indexu to tylko 20 na 2 miliony.

Przez to, gdy w zapytaniu mam " SELECT topic_id FROM tematy WHERE forum_id = 10 LIMIT 40" explain pokazuje ze mysql musi najpierw zebrac pule tematow z tego forum poprzez slaby index wiec dotyka az 30 000 roznych rekordow z tematami.

Jakie sa sposoby na pokonanie tego?
Ja dzis wpadlem na pomysl zeby stworzyc widoki (views) dla kazdego forum osobno czyli. CREATE VIEW tematy_z_forum_10 SELECT * FROM tematy WHERE forum_id = 10;
Dzieki temu zapytanie SELECT bedzie moglo pominac WHERE forum_id = 10 i bedzie wygladalo teraz tak: " SELECT topic_id FROM tematy_z_forum_10 LIMIT 40 "

Czy da to kopa? Widokow nigdy niestosowalem, jednak martwie sie ze mysql w momencie modyfikacji rekordow w tabeli tematow bedzie musial modyfikowac je podwojnie najpierw w tabeli tematy a potem w widoku tematy_z_forum_10.

Rady mile widziane i opinie o moim pomysle, czy jest moze jeszcze jakis inny sposob?

0

index na pola (forum_id, topic_id) (jeden na dwa pola!) i przed limit order by topic_id

widok nic nie da

0

Ta, tak mam, zapytanie jest rozbudowane tu tylko dalem skrot dla przykladu z tymi widokami.

Ogolnie index jest jeszcze nie optymalny bo zamiast 40 rekordow (40 tematow na strone) explain dotyka 30 000 rekordow. Bede kombinowal.

0

definicja tabel, + indexy + zapytanie i explain

0

Ok a wiec skrocilem zeby latwo bylo komus pomoc.

Tabela phpbb_topics, rekordow 1 400 000
Zapytanie aktualnie uzywane:

SELECT t.topic_id
        FROM phpbb_topics t 
        WHERE t.forum_id = 15
                AND t.topic_type <> 2
                ORDER BY t.topic_type DESC
        LIMIT 0, 40;

Explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ref forum_id_2 forum_id_2 2 const 27259 Using where

Pobiera 40 rekordow a dotyka 27259 rekordow, bardzo niewydajnie.

Struktura tabeli:
CREATE TABLE phpbb_topics (
topic_id mediumint(8) UNSIGNED NOT NULL auto_increment,
forum_id smallint(8) UNSIGNED DEFAULT '0' NOT NULL,
topic_title char(60) NOT NULL,
topic_poster mediumint(8) DEFAULT '0' NOT NULL,
topic_time int(11) DEFAULT '0' NOT NULL,
topic_views mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
topic_replies mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
topic_status tinyint(3) DEFAULT '0' NOT NULL,
topic_vote tinyint(1) DEFAULT '0' NOT NULL,
topic_type tinyint(3) DEFAULT '0' NOT NULL,
topic_first_post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
topic_last_post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
topic_moved_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
PRIMARY KEY (topic_id),
KEY forum_id (forum_id),
KEY topic_moved_id (topic_moved_id),
KEY topic_status (topic_status),
KEY topic_type (topic_type)
KEY forum_id_2 ( forum_id, topic_type ) <------------- Cardinality 98, aktualnie uzywany do tego zapytania
};

Podsumowanie:
Czemu dotyka akurat 27259 rekordow? Najoptymalniej by bylo jakby dotknal tyle ile pobiera czyli 40.
Nieumiem tego zoptymalizowac.

0

przypuszczam, że distinct topic_type nie zwróci zbyt wielu rekordów. Sortuj po czymś, co jest unikalne, np. topic_id.
zamień indeks forum_id_2 na (forum_id, topic_type, topic_id) i do order by dodaj topic_id.

0

Ciekawa koncepcja, w ten sposob jeszcze nie probowalem. Probowalem wlasnie zwiekszac unikalnosc poprzez topic_type ale tak jak zauwazyles to sa tylko 3 wartosci tylko (0,1,2) i nic nie pomagalo w praktyce. Wyprobuje to z topic_id w nocy jak ludzie beda spac i dam znac.

0

Wychodzi gorzej, nie umie skorzystac z tego indexu:

EXPLAIN SELECT t.topic_id
FROM phpbb_topics t
WHERE t.forum_id = 15
AND t.topic_type =0
ORDER BY t.topic_id DESC
LIMIT 0 , 40;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ref forum_id_2 forum_id_2 3 const,const 343932 Using where; Using index

stworzony i uzyty index:
forum_id_2 INDEX 1400000 (forum_id. topic_type, topic_id)

1 użytkowników online, w tym zalogowanych: 0, gości: 1