Theory of subqueries for 4.1.7

[Kevin]

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

[Bill Karwin]

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.

[Kevin]

>

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