Slow query

this works and doesnt produce any errors but is extremely slow, often resulting in the server throwing a timeout error

SELECT DISTINCT servicesid,short_name, long_name FROM tg_services WHERE servicesid IN (SELECT DISTINCT service_id FROM tg_srv_links WHERE service_id IN (SELECT DISTINCT service_id FROM tg_srv_links WHERE option_id = 9) AND option_id =40) AND s_approved=1 AND long_name <> '' ORDER BY `long_name` LIMIT 0, 5

is there any way to speed it up?

in a nutshell I'm using one table to store numeric form options which are linked to a 2nd table with text data

text data in the the 2nd table can have multiple numeric form options from the first table

all help much appreciated
there's got to be a better way!

re: Slow query

Hi its me again.
The query is probably slow because of the sub-queries and may be faster if re-written using JOINS, but this is not always true.
If the service_id field is a STRING field this again is a slow process.
Have you an INDEX applied on the tg_srv_links.service_id field?
This would be a far better peformance boost than any re-write.

If possible, could this key field be an INT field?
Again this would be a marked improvement.

re: Slow query

thanks again for the reply

the service_id fields are indeed int(11) fields

I've added the index as you suggested

how would you rewrite this as an inner join ?

I'm such a newb at this

thanks for the help

re: Slow query

With some difficulty.
The index has surely increased performance?

Are your tables MyISAM or InnoDB?
MyIsam is much faster for SELECT but InnoDB faster for INSERT

If I thought re-writing as a JOIN would help greatly, I would have a go.
But it is not easy and not sure how much it would help.

A few tips though Try a GROUP BY instead of DISTINCT.
They have different uses but in MySql they can work the same and I have found GROUP BY slightly faster.

Can you manage without or do differently String comparison is very slow, especially the ORDER BY

Can I suggest you Google MySQL optimisation.
You can learn more advanced tips than I have knowledge of.

If I think of anything else I will help.
Placing an INDEX on the table must have helped.

re: Slow query

hi CG
the tables are indeed MyISAM

for the
I'm trying to make sure the field has a text value
and trying to order by A-Z of long_name field

will try your suggestions this evening

thanks again, you've been a tremendous help!