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:



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

Najpierw opi..dol gościa, który to tak zaprojektował, 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.

Nie poprawi to mojej sytuacji.

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ć)? :/

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.

Przy każdym wyszukiwaniu generuj sobie reverse index w tabeli pamięciowej.
numer [int, index], tabela.id

Generowanie powinno zająć mniej niż 6-10 sekund a każde wyszukanie będzie "darmowe". Generalnie trzeba to zdenormalizować jeszcze bardziej bo niestety struktura się nie nadaje do wyszukiwania, a co dalej zależy od tego czy wyszukiwanie ma być dokładne czy nie.

który daje Ci 0 (słownie zerową) korzyść poza tym że zajmuje miejsce.

Dokładnie, indeks dla LIKE z procentami po obu stronach kompletnie nie nie daje.

Jeśli chcesz to rozwijać, to zmiana struktury bazy jest niezbędna. Może warto byłoby przerobić kod na obiektowy i logikę zapytań zrzucić na modele? Jeśli Twój projekt jest duży, to powinieneś pomyśleć poważnie o jego architekturze.