>>>>> "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.
