I want to optimize my query. Is there such tool available for MySQL?

Thanks,

Here is a sample query:

select id, username, role, first_name,last_name,email from users where
id in
(select distinct user_id from user_enrols where course_id in
(select distinct course_id from user_courses where user_id=2) and
role='S' and username like '%1%')

Steveston wrote:
> I want to optimize my query. Is there such tool available for MySQL?
>
> Thanks,
>
> Here is a sample query:
>
> select id, username, role, first_name,last_name,email from users where
> id in
> (select distinct user_id from user_enrols where course_id in
> (select distinct course_id from user_courses where user_id=2) and
> role='S' and username like '%1%')
>
>

EXPLAIN.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Nov 7, 6:14 pm, Steveston <minghu...@gmail.com> wrote:
> I want to optimize my query. Is there such tool available for MySQL?
>
> Thanks,
>
> Here is a sample query:
>
> select id, username, role, first_name,last_name,email from users where
> id in
> (select distinct user_id from user_enrols where course_id in
> (select distinct course_id from user_courses where user_id=2) and
> role='S' and username like '%1%')

Yeah -- there's no point in using "distinct" inside those sub-
queries. That's one reason to use an "in" clause in the first place.
Having said that, if you don't have a specific reason for using the
"in" clause use a join instead.

On 7 Nov, 23:14, Steveston <minghu...@gmail.com> wrote:
> I want to optimize my query. Is there such tool available for MySQL?
>
> Thanks,
>
> Here is a sample query:
>
> select id, username, role, first_name,last_name,email from users where
> id in
> (select distinct user_id from user_enrols where course_id in
> (select distinct course_id from user_courses where user_id=2) and
> role='S' and username like '%1%')

JOINS?