MySQL Forums
Forum List  »  Optimizer & Parser

Urgent Optimized QUERY needed::
Posted by: Mohammed Abdullah All Marbin
Date: March 04, 2006 03:27PM

Hi All,
I am marbin from UK. Actually i am an OCP(Oracle certified profesional). Now i am working on a project which is run under mysql 4... versions. I have a query with some conditions. I have to retrieve some record which is fetched from 5/6 tables. But the main problem is the query takes too long time. I can tried with it in oracle very easily using view and INTERSECT command. But in mysql versions doesnt support INTERSECT. I give you full query. If you guys give me a solution , how to reduce the query time or more faster with any change of my database then i will be greatful to him.

SELECT user.user_id
FROM user
where user.group_id =1
and user.user_id IN (SELECT user_id FROM technician_schedule WHERE day_id IN(1,2) GROUP BY user_id HAVING count( day_id ) =2)
AND user.user_id IN (SELECT user_id FROM technician_certification WHERE certification_id=33)
AND user.user_id IN (SELECT user_id FROM technician_sc WHERE service_category_id IN ( 1, 5, 8, 9, 10 ) GROUP BY user_id HAVING count( service_category_id)=5)
AND user.user_id IN (SELECT user_id FROM technician_tool WHERE tool_id IN ( 1, 3, 4, 5 ) GROUP BY user_id HAVING count( tool_id ) =4)
ORDER BY user.user_id

Please give me a optimal solution as soon as possible.
Hope you guys knows mysql better than me.

Thanking you

Mohammed Abdullah All Marbin
Oracle Certified Professional
United Kingdom
Cell: +447793370547

Options: ReplyQuote

Written By
Urgent Optimized QUERY needed::
March 04, 2006 03:27PM
March 04, 2006 04:33PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.