Wolne LIKE '%...%' w tabeli typu InnoDB, Niestety nie mogę użyć MyISAM Opcje

[TomASS]

Hej!

Mam zapytanie typu:

[SQL] pobierz, plaintext 
SELECT count(*) FROM tabela WHERE pole LIKE '%xyz%';
[SQL] pobierz, plaintext 

gdzie tabela ma silnik InnoDB, a pole jest typu char(200), oczywiście istnieje indeks na kolumnie "pole".
W polu "pole" trzymam numery oddzielone znakami średnika:

3123123131;5124124314;44123123;312312312;

chciałbym wyszukać czy istnieje chociaż jeden rekord zawierający jakiś numer (np 44123123).
Niestety rekordów jest 300.000 (i rośnie) a za jedną operacją (skrypt PHP) wyszukiwanie następuje około 1000razy.
Niestety zapytanie typu:

[SQL] pobierz, plaintext 
SELECT count(*) FROM tabela WHERE pole LIKE '%44123123%';
[SQL] pobierz, plaintext 

trwa 4sekundy co przy 1000 opracjach skutecznie zawiesza skrypt :/
Pewnie można by było użyć FULL-TEXT Search ale jest to tablica typu InnoDB w którym to FULL-TEXT nie występuje (IMG:style_emoticons/default/sad.gif)
Bardzo obawiam się zmienić rodzaj silnika z InnoDB na MyISAM i bardzo niechętnie to zrobię. (Wiąże się to dla mnie z kilkoma dniamia testów czy nic się nie popsuło) + audyt.

Czy ktoś ma pomysł jak zoptymalizować takie wyszukiwanie? Może ostatecznością jest wyszukać to w PHP? :/

[TomASS]

Najpierw opi..dol gościa, który to tak zaprojektował

Nie poprawi to mojej sytuacji.

a potem szybko to zmień. Dorób dodatkową tabelę "znormalizowaną", w której będziesz przechowywał informacje jeden do wielu. Np. zamiast
id pole
1 3123123131;5124124314;44123123;312312312;
będziesz miał:
rec_id pole
1 3123123131
1 5124124314
1 44123123
1 312312312
trochę ci zajmie przeorbienie tego, ale będą z tego same korzyści...
Np. krócej będziesz myślal nad zaptytaniami i będą one szybsze.

Chciałbym uniknąć takiej rewolucji. Pole to jest używane w około 30tu miejscach i aby to przerobić i przetestować musiałbym sporo czasu poświęcić.

Zastanawiam się czy ordynarna zmiana InnoDB na MyISAM niesie jakieś zagrożenia (typu zmieni mi wartości w bazie, lub założone indeksy przestaną działać)? :/

Ten post edytował TomASS 14.10.2013, 21:32:19

[sowiq]

Chciałbym uniknąć takiej rewolucji. Pole to jest używane w około 30tu miejscach i aby to przerobić i przetestować musiałbym sporo czasu poświęcić.

No niestety, @mmmmmmm podał Ci jedyne logiczne rozwiązanie. To, że jest 29 innych miejsc, w których to pole jest używane przemawia tylko za znormalizowaniem, bo problem nie zniknie magicznie po zmianie silnika tabeli, tylko będzie się nawarstwiał w miarę wzrastania ilości rekordów w tabeli. Wyszukiwanie tekstowe LIKE '%...%' zawsze będzie nieporównywalnie mniej optymalne niż wyszukiwanie wartości liczbowej w kolumnie z indeksem.

Nikt Ci nie każe przerabiać wszystkich 30 miejsc od razu, bo może to spowodować na początek sporo problemów (czyt.: błędów). Wystarczy, że zmienisz miejsce, w którym te numerki się dodają i zduplikujesz te dane - zamiast zapisywać do jednej kolumny, zapisuj tam gdzie wcześniej + do drugiej tabelki. Dzięki temu będziesz mógł stopniowo migrować wszystkie miejsca na nowe rozwiązanie.