MySQL optimisation

Very difficult query

[mcibor]

I have a search query, which uses multiple keywords and assign table.
It works as supposed, however takes quite an amount of time to retrieve small amount of data. Maybe someone knows a way of optimising the following query.

It is based on 4 tables:
data (id, data)
key(id, key)
mistake(mist, correct) - however this is used indirectly -
a(id, did, kid) - assign key.id to data.id

The query is as follows:

(SELECT data FROM data WHERE id = ANY (
SELECT a.did FROM a, key WHERE key.key='foo'
AND a.kid=key.id
) AND id = ANY (
SELECT a.did FROM a, key WHERE key.key='baar'
AND a.kid=key.id
)
ORDER BY data
)
UNION
(
SELECT data FROM data WHERE id = ANY (
SELECT a.did FROM a, key WHERE key.key='foo'
AND a.kid=key.id
) AND id = ANY (
SELECT a.did FROM a, key WHERE key.key='bar'
AND a.kid=key.id
)
ORDER BY data
)

This works as follows:
Search foo+bar should retrieve data to which is foo AND bar is assigned.
Search foo+baar should retrieve data to which is foo AND baar is assigned
and data to which foo AND bar is assigned

Hope this is clear.
My question: is there a way to make the query work faster?
Best regards
Michal Cibor

[SeanW]

Not sure if the query itself is the most efficient way to do it, but you can check how mysql is using indexes by adding EXPLAIN in front of your query and looking at that.

Sean

[mcibor]

This is the explain result. However I'm not very good about what does it mean.

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY data ALL NULL NULL NULL NULL 1836 Using where
3 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 64 Using where
3 DEPENDENT SUBQUERY key eq_ref PRIMARY PRIMARY 4 db.a.kid 1 Using where
2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 64 Using where
2 DEPENDENT SUBQUERY key eq_ref PRIMARY PRIMARY 4 db.a.kid 1 Using where
4 UNION data ALL NULL NULL NULL NULL 1836 Using where
6 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 64 Using where
6 DEPENDENT SUBQUERY key eq_ref PRIMARY PRIMARY 4 db.a.kid 1 Using where
5 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 64 Using where
5 DEPENDENT SUBQUERY key eq_ref PRIMARY PRIMARY 4 db.a.kid 1 Using where
NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL

Can anyone give means to optimise?
To take out 12 results out of 1800 the localhost needs 0.8s. I wonder what will happen later?

Best regards
Michal Cibor

[AcsCh]

It's often faster to make "joins" outside of the query, means put the join, resp. logic into the script code.

I did not study your example too detailed, maybe I got something wrong, but try:

1) retrieve first all ids in "any" and write it in a list, you then use in
2) retrieving first and second part of data separately
3) put it then together in the script.

Array funcions tend to be faaar faster than letting mysql do the job if you make a query over multiple tables.

Well I hope you understand what I mean.

Some other basic thing, but did you put proper indexes on the tables? (if you seldom insert but often select don't hesitate to put on all "searched for in the where clause" an index.)