MySQL Forums
Forum List  »  Optimizer & Parser

Slow Subquery - Optimization in this simple query needed
Posted by: elissaios
Date: March 27, 2007 11:42PM

Hello MySql.com,
I'm having a big time problem optimizing the following query:

The story:
This is basically a query used uppon execution of my mass mailer writen in PHP.
The mail-sent script uses 3 tables:

1. The user table, with stored e-mail addresses, names etc.
2. The group user table, only IDs of the group and the speficied user is stored there, in this case I will fetch users only from groups 25,26 and 27.
3. The mail log table, it contains all addresses that have already been sent, logLID below is the ID of that specific newsletter (in this case 25).


And here it goes:
------------------------------------------------------------------------------
SELECT
*
FROM
`newsme_groups_users`=a,
`newsme_users`=b
WHERE
a.gGID IN (25,26,27) AND
a.gUID=b.userID AND
b.userACTIVE='1' AND
b.userEMAIL NOT IN (SELECT logEMAIL FROM `newsme_letters_logs` WHERE `logLID`='25')
LIMIT 100
------------------------------------------------------------------------------

While executing this query I add that specific user (insert) to the log table with the letterID (25) and his email address logEMAIL, thus not listing him/her anymore in the next loop (as specified in the NOT IN subquery).

This query works great until... the log table gets 2000+ records, and thats where things start to get REALLY slow. At about 10000+ records it takes up to 2 mins to execute each loop.

If I remove the subquery line, the result return instantly. I know this is a common problem with NOT IN and subquery in it.

I've tried converting this to left outer join but with no success (no proper results), ofcourse I cant put my hand on fire I did it correctly.

Is there anyone who can support me in this matter?
Your help would be greatly appreciated.



Edited 1 time(s). Last edit at 03/27/2007 11:56PM by elissaios.

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Subquery - Optimization in this simple query needed
4295
March 27, 2007 11:42PM


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.