469,081 Members | 1,803 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,081 developers. It's quick & easy.

Theory of subqueries for 4.1.7

While converting SQL statements for a database change, I discovered a
big performance hit in MYSQL with subqueries vices Sybase. I'm hoping
that someone might be able to help me understand why?

I have two tables USERS (2200 records) and JOB Decriptions (163
records). I wanted to retrieve all the job description not in the
USER table. (No Indexes on JOBDESC currently)

select JOBDESC from JOBS where JOBDESC not in ( select JOBDESC from
USERS where JOBDESC not NULL )

In Sybase the query returns in about .56 secs
In Mysql 4.1.7 query returns in about 8.78 secs

The funny thing is if I run the query like so

select JOBDESC from JOBS where JOBDESC not in ('President','Vice
President','Treasaur','Secretary')
returns in .03 secs.

Those four descriptions are returned by the inner select.

All select statements individually return in .03 secs.
I created indexes on both tables for JOBDESC and reduced the time to
..97 secs. I used explain command to help understand what is going on
hence the indexes, but why the difference in speed?

I would appreciate any help.

Kevin
Jul 20 '05 #1
2 2072
Kevin wrote:
select JOBDESC from JOBS where JOBDESC not in ( select JOBDESC from
USERS where JOBDESC not NULL )


The MySQL documentation page
http://dev.mysql.com/doc/mysql/en/Op...ubqueries.html claims that
it executes non-correlated subqueries only once, instead of executing it
while evaluating every row of the outer query. But you should make sure
that your subquery is being interpreted as a non-correlated subquery.

For instance, use row aliases to distinguish between the two JOBDESC fields:

select J.JOBDESC from JOBS AS J
where J.JOBDESC not in (
select U.JOBDESC from USERS AS U
where U.JOBDESC is not NULL
)

Since you have a JOBDESC column in both tables, JOBS and USERS, it might
be interpreting the reference to JOBDESC in the subquery as though you
had written your query thus:

select J.JOBDESC from JOBS AS J
where J.JOBDESC not in (
select U.JOBDESC from USERS AS U
where J.JOBDESC is not NULL
)

But I would also point out that you needn't write this query with
subqueries at all. Try it as an outer join and see what the performance
is like:

select DISTINCT J.JOBDESC from JOBS AS J
LEFT OUTER JOIN USERS AS U ON J.JOBDESC = U.JOBDESC
where U.JOBDESC is NULL

Regards,
Bill K.
Jul 20 '05 #2
>
select DISTINCT J.JOBDESC from JOBS AS J
LEFT OUTER JOIN USERS AS U ON J.JOBDESC = U.JOBDESC
where U.JOBDESC is NULL


Thanks for you feedback... Before I upgraded the database server for
3.23, I tried this exact query with similar results. This was one of
the reasons i upgraded hoping that subqueries would have resulted in a
faster query time, but with no luck.

Of course creating the indexes solved my problems. It was just
unexpected result. All tables were created as they currently existed.

Happy Holidays...

kevin
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by pete | last post: by
6 posts views Thread by Daniel Elliott | last post: by
2 posts views Thread by orin | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.