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

[Gunnar Beister]

Hi,

it might by a silly question, but I'm struggeling since days to find a perfect and FAST! solution.
I have an "article" table with product-name, id and price. I have a second table which just stores a reference to an article-id(ref_article) of the first article-table and a STRING field for an "EAN"-number (articles might have that). The third table is very similar but instead of an "EAN"-number I handle an "ISBN"-number.
As an article can have n EAN-numbers and n ISBN-numbers I separated those from the article table in those two extra tables.

I do have a search-field on a GUI-form which allows search for articles via product-name and EAN and ISBN. As I don't want to check first whether search-string is a set of words or an EAN or an ISBN I want to setup a query which handles this in one.

As e.g. a search via keywords might have more than word, I need to handle this via a php script which creates my query.

My current query (assuming a search via two words) looks as following:

select
t1.id as id,
t1.title as title,
t.price as price,
t2.ean as ean,
t3.isbn as isbn
from articles t1
left join ean_map as t2 on t2.ref_article = t1.id
left join isbn_map as t3 on t3.ref_article = art.id
where ((t1.title like '%word 1%' or t2.ean like '%word 1%' or t3.isbn like '%word 1%') and (t1.title like '%word 2%' or t2.ean like '%word 2%' or t3.isbn like '%word 2%'))

Running this query takes ages and not acceptable. What I'm doing wrong here?
Thanks a lot!