✔ MySQL performance mit "like"

[BaseBallBatBoy]

Hallo!

Ich habe ein MySQL Query das ein bestimmtes Feld (internal_ans: enthält einen Freitext mit jeweils mehreren tausend Zeichen) nach einem Keyword durchsucht.

Das Problem: das Query ist extrem langsam, dh. mehrere Minuten.

Die betroffene Tabelle "ianswers" hat einen Umfang von 9123 Zeilen bei einer Grösse von 21.5 MB - stetig wachsend natürlich .

Das Query fragt auch noch andere Dinge ab und verwendet joins, aber das langsame daran ist eindeutig like. Der Vollständigkeit zuliebe zeige ich aber das ganze Query.

Hat mir jemand einen Vorschlag was ich daran verbessern könnte damit das Query in einer akzeptablen Zeit ausgeführt werden kann? Bin dankbar um jede Idee.

select cef.number, a.first_name, a.last_name, cef.severity, cef.date, cef.slogan, cl.customer_name, r.name, r.code, cef.product, cef.group, fa.name, cef.feature, cef.turnaroundtime2, cef.status
from customer_entry_form cef 
left join cef_region cr on cef.number=cr.number 
left join region r on cr.region_id = r.id 
left join functional_area fa on cef.functional_area=fa.id 
left join product pd on cef.product=pd.name
left join agents a on cef.assignee=a.id  
left join ianswers ia on cef.number=ia.number, 
customer_lookup cl 
where cef.customer_id = cl.id 
and ia.internal_ans like('%KEYWORD%')     
order by cef.date desc

Gruss
BBBB

 

[Yaslaw]

item: mache den LIKE in einer Unterabfrage
item: binde diesen mit INNER JOIN an
item: Auch cl kannst du mit INNER JOIN anbinden

SELECT
	....
FROM
	customer_entry_form cef 
	INNER JOIN (
			SELECT
				ianswers.number
			FROM
				ianswers
			WHERE
				ianswers.internal_ans LIKE('%KEYWORD%')
		) AS ia
		ON cef.number=ia.number
	INNER JOIN customer_lookup cl
		ON cef.customer_id = cl.id
	LEFT JOIN cef_region cr 
		ON cef.number=cr.number
	LEFT JOIN .....

Nachtrag:
ggf kommst du mit MATCH() AGAINST und einem FULLTEXT-Index auf die Spalte internal_ans schneller

SELECT
	....
FROM
	customer_entry_form cef 
	INNER JOIN (
			SELECT
				ianswers.number
			FROM
				ianswers
			WHERE
				MATCH(ianswers.internal_ans) AGAINST ('KEYWORD')
		) AS ia
		ON cef.number=ia.number
	INNER JOIN customer_lookup cl
		ON cef.customer_id = cl.id
	LEFT JOIN cef_region cr 
		ON cef.number=cr.number
	LEFT JOIN .....

 

[BaseBallBatBoy]

Hallo yaslaw!
Danke für die schnelle Antwort!

Mir ist gerade aufgefallen, dass ich ein wichtiges Detail vergessen habe, welches deine elegante Lösung etwas torpediert. Ich möchte mich an dieser Stelle dafür bei dir entschuldigen, sorry.

Folgendes ist falsch:

AND ia.internal_ans LIKE ('%KEYWORD%')

Stattdessen brauche ich:

AND (cef.slogan LIKE ('%KEYWORD%') OR  ia.internal_ans LIKE ('%KEYWORD%'))

Macht das ganze natürlich etwas komplizierter. Siehst du aber dennoch einen Weg wie sich das in deine Idee integrieren lässt?

Gruss
BBBB

 

[Yaslaw]

In welche der beiden Ideen?
Für IDee 1 hast du die Lösung ja selber geschrieben

Für den MATCH() nun, schau in der Doku nach....

 MATCH(cef.slogan,ianswers.internal_ans) AGAINST ('KEYWORD')

Das einzige was du berücksichtigen musst, dass ebi MATCH nur ganze Wörter gefunden werden.
Mit 'kran' findest du also kein 'Baukran'

 

[BaseBallBatBoy]

Hallo yaslaw!

In welche der beiden Ideen?
Für IDee 1 hast du die Lösung ja selber geschrieben

Für die erste Idee.

Und leider nein, das ist noch nicht die Lösung, weil ja der inner join dafür sorgt, dass nur datasets übernommen werden, die garantiert das Keyword bereits in ianswers.internal_ans enthalten.

Die zusätzliche Zeile die ich geschrieben habe macht danach gar nichts mehr da, da ianswers.internal_ans ja immer das Keyword enthält.

Es sind also gar keine datasets möglich, die das Keyword nur in cef.slogan haben, nicht aber in ianswers.internal_ans.

Für den MATCH() nun, schau in der Doku nach....

 MATCH(cef.slogan,ianswers.internal_ans) AGAINST ('KEYWORD')

Das einzige was du berücksichtigen musst, dass ebi MATCH nur ganze Wörter gefunden werden.
Mit 'kran' findest du also kein 'Baukran'

Das ist exakt der Grund warum ich MATCH/AGAINST nicht verwenden kann. Ich brauche die Wildcards vor und nach dem Keyword...

 

[Yaslaw]

Dann füge sie im Untersql bereits zusammen

SELECT
	....
FROM
	(
		SELECT 
			cef.*
		FROM
			customer_entry_form cef
			INNER JOIN ianswers ia ON cef.number=ia.number
		WHERE
			(cef.slogan LIKE ('%KEYWORD%') OR  ia.internal_ans LIKE ('%KEYWORD%'))
	) AS cef_ia
    INNER JOIN customer_lookup cl
        ON cef.customer_id = cl.id
    LEFT JOIN cef_region cr 
        ON cef.number=cr.number
    LEFT JOIN .....

 

[BaseBallBatBoy]

Dann füge sie im Untersql bereits zusammen

Wie recht du hast

Ist nun eindeutig scheller. Was vorhin mehrere Minuten dauerte, braucht nun noch ca. 40 Sekunden. Ist zwar immer noch viel, angesichts der Datenmenge die like-wise durchsucht werden muss aber akzeptabel.

Vielen Dank!