Witam!

Mam tabele fields o nastepujacej strukturze i okolo 2mln wierszy:

id(int), document_id(int), name(varchar), value(text)
index(document_id, name, id)

W tabeli trzymane sa nazwy pol i ich wartosci z refem do dokumentu. Dokument ma tak jakby wersje, pole jest edytowane poprzez wstawienie wiersza o refie do tego samego dokumentu z ta sama nazwa. czyli jesli mam wiersz:

1 | 1 | imie | adrian

i wstawiam

2 | 1 | imie | marek

to znaczy ze wyedytowalem dokument '1' i zmienilem wartosc pola imie z adrian na marek.

aktualne wartosci dla poszczegolnych pol jestem w stanie wyciagnac bardzo szybko dzieki indexowi (0.07 s):

[SQL] pobierz, plaintext 
SELECT max(d.id), d.document_id, d.name, d2.value FROM documents d, documents d2 WHERE d.id = d2.id GROUP BY d.documentId, d.name
[SQL] pobierz, plaintext 

ale kiedy chce przeszukac wyniki po value zapytaniem:

[SQL] pobierz, plaintext 
SELECT STRAIGHT_JOIN <span style ='color:blue'>max(d.id), d.documentId, d.name, d2.value FROM documents d, documents d2 WHERE d2.name = 'first_name' AND d2.value = 'Adrian' AND d.id = d2.id GROUP BY documentId, name
[SQL] pobierz, plaintext 

to wykonanie trwa juz okolo 15s.

Ma ktos jakis pomysl?? bo mi sie juz wyczerpaly

pozdrawiam

adrian

Spróbuj założyć 2-kolumnowy indeks na kolumny name oraz value. Powinno pomóc.

Dzieki za odpowiedz

Niestety wyniki nie poprawily sie wogle. Zreszta dawanie indexu na pole value, ktore jest typu text w mysq-u troche mija sie z celem bo trzeba okreslic jego dlugosc, a naksymalna dlugosc indexu to 1000 bajtow, a poniewaz baza ma kodowania utf-8 (1 znak = 3 bajty) wiec mamy raptem 333 znaki :/

To co dziwi mnie najbardziej to to ze wybranie ostatnich wartosci dla kazdego pola dokumentu zajmuje 0.07, przeszukanie calej tabeli w poszukiwaniu wartosci value = 'Adrian' jakies 0.8 a wyciagniecie czesci wspolnej tych dwoch zbiorow jest tak masakrycznie wolne...

Sposób:

[SQL] pobierz, plaintext 
SELECT * FROM tabela1, tabela2 WHERE tabela1.id = tabela2.id
[SQL] pobierz, plaintext 

nie jest najlepszym rozwiązaniem przy takiej ilości danych. A to z prostej przyczyny. Pierwsza część zapytania (FROM tabela1, tabela2) tworzy iloczyn kartezjański dwóch tabel, czyli jak masz po 1 mln rekordów, to na początku robi się 1mln^2 wyników. Dopiero WHERE tabela1.id = tabela2.id wybiera z tego iloczynu interesujące Cię wyniki. GROUP BY dodatkowo obciąża zapytanie.

Zainteresuj się składnią JOIN - miałem bardzo podobny problem i po zamianie iloczynu kartezjańskiego na JOIN'y zszedłem z 8 sek. do 0.02 sek.

[edit]
Kiedyś pisałem o tym na Gronie, może znajdziesz tam kilka wskazówek: http://grono.net/forum/topic/13590469/0/ (jak nie masz konta, to zrobię screen'a)

Mozna by bylo to zapytanie zlozyc tak, ale ono wtedy nie zwraca mi wynikow ktore potrzebuje:

[SQL] pobierz, plaintext 
SELECT SQL_NO_CACHE max( df.id ) , df.documentId
FROM FIELDS df
JOIN FIELDS df2 ON ( df2.name = 'machine'
[sql]AND df.id = df2.id )
WHERE df2.value LIKE '%424591%'
GROUP BY df.documentId, df.name
LIMIT 0, 30
[SQL] pobierz, plaintext 

Mi chodzi o to zeby uwzglednic tylko ostatnia 'wersje' pola dokumentu a zapytanie powyzej zwraca dokument ktory kiedys mial w polu machine wartosc 424591, czyli zupelna pomylka

Z kolei to zapytanie ktore zwraca poprawne wyniki:

[SQL] pobierz, plaintext 
SELECT df2.documentId
FROM ( SELECT max( df.id ) AS id
FROM documents df
GROUP BY df.documentId, df.name
) AS nasza
JOIN documents df2 ON ( df2.name = 'machine' AND df2.value LIKE '%424591%' AND nasza.id = df2.id )
[SQL] pobierz, plaintext 

trwa az 18.5s. Tutaj wybieram najpierw ostatnie idiki pola o danej nazwie dla danego dokumentu i dopiero potem lacze je z wynikami przeszukiwania co daje oczekiwany rezultat ale trwa straaaasznie dlugo

baza danych nie służy do wyszukiwania. Zapytania z LIKE zawsze będą wolne dla większej ilości rekordów. Użyj np. sphinxa

tutaj nie jest problemem like bo to zapytanie ktore ma w sobie like wykonuje sie w 0.5 s

[SQL] pobierz, plaintext 
SELECT SQL_NO_CACHE max( df.id ) , df.documentId
FROM FIELDS df
JOIN FIELDS df2 ON ( df2.name = 'machine'
[sql]AND df.id = df2.id )
WHERE df2.value LIKE '%424591%'
GROUP BY df.documentId, df.name
LIMIT 0, 30
[SQL] pobierz, plaintext 

Jak napisałem powyżej - GROUP BY może znacząco wydłużyć czas wykonywania zapytania. Usuń dla testów wszystkie GRUP BY i zobacz jaki będzie czas. Jeśli się zmniejszy (tak przewiduję), to będziesz wiedział co optymalizować (np. przez DISTINCT).

distinct dla mysql jest wolniejszy niz group by.

Poza w podzapytaniu wybierajacym ostatnie wartosci dla kazdego pola, kazdego dokumentu, czyli

[SQL] pobierz, plaintext 
SELECT max( df.id ) AS id
FROM FIELDS df
GROUP BY df.documentId, df.name
[SQL] pobierz, plaintext 

jest zastosowany tight scan, dzieki temu ze na tabeli zalozony jest index (documentId, name, id) i to zapytanie zwraca wyniki w 0.07s wiec watpie zeby dalo sie szybciej. Wg mnie problem tkwi w tym ze jesli majac juz teraz te ostatnie wersje kazdego chce sprawdzic czy sa wsrod nich takie z value like '%234%' musze jeszcze raz dolaczyc tabale i po niej szukac, czyli napisac cos takiego

[SQL] pobierz, plaintext 
SELECT id FROM FIELDS WHERE value LIKE '%123%'
[SQL] pobierz, plaintext 

--> czas wykonania to 0.05s

teraz z jednej strony mam zbior idikow pol w ostatniej wersji z drugiej zbior idikow pol ktore w value kiedys tam (moze obecnie) maja 123.

Wyciagniecie czesci wspolnej tych dwoch zbiorow czyli fieldow ktore aktualnie maja w wartosci 123 zajmuje tyle czasu.

Jak zagladam w logi mysql-a to okazuje sie ze jedna z tabel jest kopiowana na dysk jako tabela tymczasowa i to raczej trzeba optymalizowac tylko problem w tym ze juz nie mam pomyslu jak, bo probowalem chyba kazdej znanej mi skladni...