A tough MySQL question having to do with LEFT JOIN but to exclude

Databases
1

Hey all,

So this is a tough MySQL question for MySQL GODs here
Say we have a Table called client_data, which contains base client data such as: id, address, email etc.

We have a Table called client_contacts, which contains emails we have sent to clients based on XYZ reasons with data such as: client_id, email, reason_id, etc.

How can we generate a SELECT which is based on clients from Table client_data & Table client_contacts which contains clients that have NOT been emailed to for a given reason_id

We tried this, but 1st it does not work and 2nd it Kills MySQL:

SELECT cd.email, cd.id, cd.category FROM client_data AS cd
LEFT JOIN client_contacts AS cc ON (cd.email != cc.email)
WHERE cc.reason_id = 991 AND cd.category IN (49, 149) ORDER BY RAND() LIMIT 10000;

Thanks

2 
SELECT cd.email
     , cd.id
     , cd.category 
  FROM client_data AS cd
LEFT OUTER
  JOIN client_contacts AS cc 
    ON cc.email = cd.email
   AND cc.reason_id = 991 
 WHERE cd.category IN (49, 149) 
   AND cc.email IS NULL
3

r9, this does work as far as getting the results goes.
But it sends MySQL usage from typical of 3.5% of CPU to 105% of CPU and it took about 2 Minutes for answers to come back and that was with LIMIT only at 10. And we need typical such emails to be at 5000 or so LIMIT

So obviously this SELECT as is is not practical, any ideas to get it to perform like typical SELECT which returns result in under 0.02 sec and barely effects CPU?

Thanks

4

yes, i do

make sure you have the right indexes on your tables

1 Like
5

Can you be more specific about that?

6

sure

indexes allow the database engine to find stuff really fast, rather than having to access the actual rows to inspect them

see database table scan optimization

1 Like
7

Of course we know about Indexed, but questions are:
1- Which fields to Index
2- And should we shut down a DB while it is being Indexed
3- Will it really make that much of a difference since our typical select returns results in 0.005 second for 10000+ records and this select took 2 minutes for 10 records

Thanks

8

start with the join columns

then try the WHERE columns

nope

yup

9

r9, sorry for long delay in replying. This is what you get for doing 100 peoples job.
Anyway, to your points:
Can you point me to what you consider to be best writeup on Mysql Indexing as it pertains to above.
And how would you issue such as command and what options should we worry about?

Regards,

10

best article on indexing? right here → Use The Index Luke (Dot Com)

1 Like
11

r9, just wanted to say Thanks on this Index heads up.
It has been a real Eye opener to delve deep into Indexs and boy we made some SELECTs much faster. Like 100 times faster! But one Key thing I am having difficult grasping with is:
Which Columns to Index? For best results. I mean what is best logic for deciding this?

Thanks,

12
  1. Which fields are searched for most commonly?
  2. Which fields are FKs to other tables? This could be one or more fields in an index.

Those are typically the two biggest places where indexes have impact.

13

Dave, thanks for those clear answers.
From that, for sure we are going to Index the email fields

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.