Hi all,
I'm having a problem where the optimiser seems to take a *very* long time to
work out which indexes to use.
Although I'm using a lot of joins, it takes around 10 minutes to give an
EXPLAIN of the query shown below. The actual query takes only a second or
two (or so it appears).
linklist is a table which contains 5.5 million rows. Position only contains
95,000.
Anyone have any ideas ? The query is as simple as it can be, and speed is
very important, as it returns immediate data to users. Apologies for the
size of the SQL, I can't see any way to reduce it.
select
DISTINCT position.id as total
from
position,
linklist ,
linklist as linklist2,
linklist as linklist3,
linklist as linklist4,
linklist as linklist5,
linklist as linklist6,
linklist as linklist7,
linklist as linklist8,
linklist as linklist9,
linklist as linklist10,
linklist as linklist11,
linklist as linklist12,
linklist as linklist13
where
position.id = linklist.position_id
and linklist.position_id = linklist2.position_id
and linklist.position_id = linklist3.position_id
and linklist.position_id = linklist4.position_id
and linklist.position_id = linklist5.position_id
and linklist.position_id = linklist6.position_id
and linklist.position_id = linklist7.position_id
and linklist.position_id = linklist8.position_id
and linklist.position_id = linklist9.position_id
and linklist.position_id = linklist10.position_id
and linklist.position_id = linklist11.position_id
and linklist.position_id = linklist12.position_id
and linklist.position_id = linklist13.position_id
and (linklist.id = '705' and linklist2.id = '125'
and linklist3.id = '1244' and linklist4.id = '4246'
and linklist5.id = '803' and linklist6.id = '1247'
and linklist7.id = '1904' and linklist8.id = '3509'
and linklist9.id = '69' and linklist10.id = '952'
and linklist11.id = '5' and linklist12.id = '3071' )
and (position.dater >= 968)
and (linklist13.id = '4' )
Many thanks,
Russ.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
