List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 11 1999 6:39am
Subject:Optimizer problems
View as plain text  
>>>>> "Jochen" == Jochen Wiedmann <joe@stripped> writes:

Jochen> Given the following

Jochen>   EXPLAIN SELECT PROFILES_IP.ID_PROFILE, PROFILES.ID FROM
Jochen>   PROFILES_IP LEFT JOIN PROFILES ON PROFILES_IP.ID_PROFILE=PROFILES.ID

Jochen>   table   type    possible_keys   key     key_len ref     rows    Extra
Jochen>   PROFILES_IP     index   NULL    ID_PROFILE      4       NULL    3      
Jochen> Using index
Jochen>   PROFILES        eq_ref  PRIMARY PRIMARY 4       PROFILES_IP.ID_PROFILE  1 
Jochen> Using index

Jochen> I'd expect a similar result in the more complicated

Jochen>   EXPLAIN SELECT SCANS.RISC, PROFILES.ID AS PROF_ID,SCANS.ID,
Jochen>   PROFILES.NAME AS PROF, SCANS.NAME as SCAN ,SCANS_BUNDLE.NAME
Jochen>   as BUNDLE, SCANS_DONE.SCAN_TIME, SCANS_DONE.SCAN_TIME_STOP,
Jochen>   SCANS_DONE.PING_SUCCESS, SCANS_DONE.SCAN_PERFORMED,
Jochen>   SCANS_DONE.SCAN_RESULT, SCANS_DONE.IP FROM SCANS_DONE straight_join
Jochen>   SCANS straight_join SCANS_BUNDLE left join PROFILES_IP on
Jochen>   in_aton(SCANS_DONE.IP) between in_aton(PROFILES_IP.FIRST_IP) AND
Jochen>   in_aton(PROFILES_IP.LAST_IP) left join PROFILES on
Jochen>   PROFILES_IP.ID_PROFILE=PROFILES.ID where (SCANS_DONE.ID_SCAN=SCANS.ID
Jochen>   AND SCANS.ID_BUNDLE=SCANS_BUNDLE.ID) AND (NULL  or
Jochen>   SCANS_DONE.SCAN_RESULT=0 or SCANS_DONE.SCAN_RESULT=1 or
Jochen>   SCANS_DONE.SCAN_RESULT=2) AND (SCANS_DONE.SCAN_TIME >DATE_SUB(now(),
Jochen>   INTERVAL 2 DAY)) ORDER BY SCANS_DONE.SCAN_TIME

Jochen> However, it seems the optimizer doesn't recognize the relationship
Jochen> between PROFILES and PROFILES_ID here, although the ON clause is still
Jochen> the same. Any ideas?

Jochen>   table   type    possible_keys   key     key_len ref     rows    Extra
Jochen>   SCANS_DONE      range   scanTimeIndex   scanTimeIndex   NULL    NULL   
Jochen> 19438
Jochen>   SCANS   eq_ref  PRIMARY PRIMARY 4       SCANS_DONE.ID_SCAN      1
Jochen>   SCANS_BUNDLE    eq_ref  PRIMARY PRIMARY 4       SCANS.ID_BUNDLE 1
Jochen>   PROFILES_IP     ALL     NULL    NULL    NULL    NULL    3
Jochen>   PROFILES        ALL     PRIMARY NULL    NULL    NULL    4

Hi!

Could you mail me the output from:

mysqldump --no-data

for the above tables so that I can test this.

Regards,
Monty

PS: How many entries has 'PROFILES' in the second case.  If there is
    only 4 rows in this table MySQL may decide to use 'ALL' instead of 
    'eq-ref' in some context.  The speed difference between the above should
    normally be neglectable.
    To make the above query faster, you could store the value of
    in_aton() in PROFILES_IP as MySQL can't use a key on PROFILES_IP.

PPS: Sorry for the late replay, but I was in China on vacation for a
     week.
Thread
Optimizer problemsJochen Wiedmann6 May
  • Optimizer problemsMichael Widenius11 May