Very slow SQL statement

Question

I'm having some major issues with an SQL statement, the following statement causes so much stress to the MySQL engine it pretty much hangs:

select c.title, c.initial, c.surname, c.labelno, c.email, br1.bookingdate
from explorer.booking_record br1
inner join explorer.client c
on c.labelno = br1.labelno
and email not like ''
where br1.bookingdate >= '2009-01-01'
and br1.bookingdate < '2009-01-31'
and c.labelno Not In (Select labelno from explorer.booking_record br2 where br2.labelno = br1.labelno and br2.bookingdate >= '2010-01-01' and br2.bookingdate < '2010-01-31')

I've tried a few variations on the same, without the joins and two sub-statements, adding 'order by' as advised by the documentation. There aren't actually that many records in the database, booking_record has ~500,000 records and client has ~450,000. If I let the query run it's usually got about 20 results after 70-80 seconds, but this causes the service to go into a loop-like state.

Any advice would be much appreciated.

Daniel.

Answer 1

NOT LIKE and NOT IN are most likely the culprits here.

Change NOT LIKE to <>. You do not need any 'LIKE' behaviour here, since you're not using any wildcard characters, so you can simply change this to the 'not equal' operator.

Next to that, have you already taken a look at the execution plan and have you investigated whether you have created indexes on the columns that could use an index ?

Answer 2

Did you already put indexes on the where fields? Not in and not like is known to be slow, so maybe you can avoid this statements.

Answer 3

Make sure that the following columns are indexed:

explorer.booking_record.labelno
explorer.booking_record.bookingdate
explorer.booking_record.labelno
explorer.client.labelno
explorer.client.email

Now try the following query:

select c.title, c.initial, c.surname, c.labelno, c.email, br1.bookingdate
from explorer.booking_record br1
left outer join explorer.booking_record br2 on br2.labelno = br1.labelno
    and br2.bookingdate >= '2010-01-01' and br2.bookingdate < '2010-01-31'
inner join explorer.client c on c.labelno = br1.labelno and c.email <> ''
where br1.bookingdate >= '2009-01-01'
    and br1.bookingdate < '2009-01-31'
    and br2.labelno is null

Answer 4

'Not in' tends be be slow. Try something like this, make sure labelno is indexed in both tables.

select c.title, c.initial, c.surname, c.labelno, c.email, br1.bookingdate 
from explorer.booking_record br1
inner join explorer.client c
on c.labelno = br1.labelno
and email not like ''
where br1.bookingdate >= '2009-01-01'
and br1.bookingdate < '2009-01-31'
and Not Exists (Select 1 from explorer.booking_record br2 where br2.labelno = br1.labelno and br2.bookingdate >= '2010-01-01' and br2.bookingdate < '2010-01-31')