Re: select over a table joing two others via same column is very slow

[Gunnar Beister]

Hi Peter and Rick,


first sorry for not responding earlier to your last post.
Thanks a lot for your fantastic tips, which helped very much.
Thanks to your tips I was able to resolve my problem.
I end up by removing the ID-field from my map tables (ean- and isbn-tables) and creating a pair of unique id via (ref_article and ean or isbn). Further more I changed by data-model to InnoDB for the articles-, ean-, and isbn-table.
For the query I changed to 'regexp' instead of 'like' together with wildcards.

So my query looks like:

SELECT
t1.title as title,
t1.id as id
from articles t1
join ean_map as t2 on t1.id = t2.ref_article
join isbn_map as t3 on t1.id = t3.ref_article
where t1.title regexp ('some') and t1.title regexp('words')
or
(t2.ean regexp ('some') and t2.ean regexp('words'))
or
(t3.isbn regexp ('some') and t3.isbn regexp('words'))

now the query takes less than a second to run...compared to minutes before :-D

Thanks a lot!