SQL query explain

[Yeo Wee tat]

Hi all ,
I have sql query below which caused bottlneck in our performance.
I suspected it was caused by tb_travelitinerary_archive table which can store more than 800+ thousands records
I have added the indexes however the sql query still slow and the type is ALL when EXPLAIN in the mysql.
I have also include the EXPLAIN and TABLE schema and indexes. Anyone have any ideas or suggestions are much appreciated. Thanks.

SELECT `r`.`REGID` AS `REGID`,`r`.`TYPE` AS `TYPE`,`r`.`OSUSUBSCRIPTION` AS `OSUSUBSCRIPTION`,
`r`.`GROUPSIZE` AS `GROUPSIZE`,`r`.`GROUPNAME` AS `GROUPNAME`,`r`.`STATUS` AS `STATUS`,`t`.`NRIC` AS `NRIC`,
`t`.`NRICTYPE` AS `NRICTYPE`,`t`.`FIRSTNAME` AS `FIRSTNAME`,`t`.`LASTNAME` AS `LASTNAME`,`t`.`GENDER` AS `GENDER`,
`t`.`DATEOFBIRTH` AS `DATEOFBIRTH`,`t`.`PASSPORT` AS `PASSPORT`,`t`.`MOBILE` AS `MOBILE`,`t`.`EMAIL` AS `EMAIL`,
`t`.`NOK_NAME` AS `NOK_NAME`,`t`.`NOK_RELATIONSHIP` AS `NOK_RELATIONSHIP`,`t`.`NOK_CONTACTNUMBER` AS `NOK_CONTACTNUMBER`,
`t`.`NOK_MOBILE` AS `NOK_MOBILE`,`t`.`NOK_ADDRESS` AS `NOK_ADDRESS`,
`i`.`COUNTRYCODE` AS `COUNTRYCODE`,ifnull(`i`.`COUNTRYNAME`,`m`.`COUNTRYNAME`) AS `COUNTRYNAME`,`i`.`STATECODE` AS `STATECODE`,
ifnull(ifnull(`i`.`STATENAME`,`s`.`ISONAME`),_UTF8 '') AS `STATENAME`,`i`.`CITYCODE` AS `CITYCODE`,
ifnull(ifnull(`i`.`CITYNAME`,`c`.`CITYNAME`),_UTF8 '') AS `CITYNAME`,`i`.`FROMDATE` AS `FROMDATE`,`i`.`TODATE` AS `TODATE`,
`i`.`FOREIGNADDRESS` AS `FOREIGNADDRESS`,`i`.`HOTEL` AS `HOTEL`,`i`.`CONTACTNUMBER` AS `CONTACTNUMBER`,
`i`.`MOBILECOUNTRY` AS `MOBILECOUNTRY`,`i`.`MOBILEAREA` AS `MOBILEAREA`,`i`.`MOBILENUMBER` AS `MOBILENUMBER`,
`n`.`NATUREOFTRAVEL` AS `NATURECODE`,`n`.`DESCRIPTION` AS `NATUREOFTRAVEL`,`z`.`ZONECODE` AS `ZONECODE`,
`z`.`DESCRIPTION` AS `ZONE`,ifnull(`o`.`OMCODE`,ifnull((SELECT `eregister`.`tb_omcitymapping`.`OMCODE` AS `OMCODE`
FROM `eregister`.`tb_omcitymapping`
WHERE ((`eregister`.`tb_omcitymapping`.`CITYCODE` = _UTF8 'OTH') AND
(`eregister`.`tb_omcitymapping`.`COUNTRYCODE` = `i`.`COUNTRYCODE`))),_UTF8 'MFA')) AS `OMCODE`,
ifnull(`om`.`OMNAME`,(SELECT `vw_overseasmission`.`OMNAME` AS `OMNAME` FROM `eregister`.`vw_overseasmission`
WHERE (`vw_overseasmission`.`MISSIONID` = (SELECT `eregister`.`tb_omcitymapping`.`OMCODE` AS `OMCODE`
FROM `eregister`.`tb_omcitymapping` WHERE ((`eregister`.`tb_omcitymapping`.`CITYCODE` = _UTF8 'OTH') AND
(`eregister`.`tb_omcitymapping`.`COUNTRYCODE` = `i`.`COUNTRYCODE`)))))) AS `OMNAME`,
`r`.`ARCHIVETYPE` AS `ARCHIVETYPE` FROM ((((((((((((
`eregister`.`tb_travelitinerary_archive` `I` LEFT JOIN `eregister`.`vw_country` `M`
ON((`i`.`COUNTRYCODE` = `m`.`COUNTRYCODE`)))
LEFT JOIN `eregister`.`vw_state` `S` ON((`i`.`STATECODE` = `s`.`STATECODE`)))
LEFT JOIN `eregister`.`vw_city` `C` ON((`i`.`CITYCODE` = `c`.`CITYCODE`)))
LEFT JOIN `eregister`.`tb_registration_archive` `R` ON((`i`.`REGID` = `r`.`REGID`)))
LEFT JOIN `eregister`.`tb_registrant_archive` `T` ON((`r`.`REGID` = `t`.`REGID`)))
LEFT JOIN `eregister`.`tb_natureoftravel` `N` ON((`n`.`NATUREOFTRAVEL` = `r`.`NATUREOFTRAVEL`)))
LEFT JOIN `eregister`.`tb_omcitymapping` `O` ON(((`i`.`COUNTRYCODE` = `o`.`COUNTRYCODE`) AND (`i`.`CITYCODE` = `o`.`CITYCODE`))))
LEFT JOIN `eregister`.`vw_overseasmission` `OM` ON((`o`.`OMCODE` = `om`.`MISSIONID`)))
LEFT JOIN `eregister`.`tb_wardenregistrantmapping_archive` `WM` ON(((`wm`.`CITYCODE` = `c`.`CITYCODE`) AND (`wm`.`REGISTRANTNRIC` = `t`.`NRIC`) AND (`wm`.`ITINERARYID` = `i`.`ITINERARYID`))))
LEFT JOIN `eregister`.`tb_wardenzonemapping` `ZM` ON(((`zm`.`CITYCODE` = `c`.`CITYCODE`) AND (`zm`.`WARDENID` = `t`.`NRIC`))))
LEFT JOIN `eregister`.`tb_zone` `Z` ON(((`wm`.`ZONECODE` = `z`.`ZONECODE`) OR (`zm`.`ZONECODE` = `z`.`ZONECODE`))))
LEFT JOIN `eregister`.`tb_cityzonemapping` `CZ` ON(((`cz`.`ZONECODE` = `z`.`ZONECODE`) AND (`cz`.`CITYCODE` = `c`.`CITYCODE`))));


TABLE tb_travelitinerary schema:
mysql> describe tb_travelitinerary_archive;
+----------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+----------------+
| ITINERARYID | int(11) | NO | PRI | NULL | auto_increment |
| REGID | int(11) | NO | MUL | NULL | |
| FROMDATE | datetime | NO | | NULL | |
| TODATE | datetime | NO | | NULL | |
| COUNTRYCODE | varchar(3) | NO | MUL | NULL | |
| COUNTRYNAME | varchar(255) | YES | | NULL | |
| STATECODE | varchar(100) | YES | | NULL | |
| STATENAME | varchar(255) | YES | | NULL | |
| CITYCODE | varchar(100) | YES | | NULL | |
| CITYNAME | varchar(255) | YES | | NULL | |
| CONTACTNUMBER | varchar(20) | YES | | NULL | |
| MOBILECOUNTRY | varchar(4) | YES | | NULL | |
| MOBILEAREA | varchar(4) | YES | | NULL | |
| MOBILENUMBER | varchar(20) | YES | | NULL | |
| FOREIGNADDRESS | varchar(1000) | YES | | NULL | |
| HOTEL | varchar(255) | YES | | NULL | |
| CREATEID | varchar(100) | NO | | NULL | |
| CREATEDT | datetime | NO | | NULL | |
| MODIFIEDID | varchar(100) | YES | | NULL | |
| MODIFIEDDT | datetime | YES | | NULL | |
+----------------+---------------+------+-----+---------+----------------+
20 rows in set (0.02 sec)

INDEXES for tb_itinerary table:

mysql> show indexes from tb_travelitinerary_archive;
+----------------------------+------------+------------------+--------------+---
----------+-----------+-------------+----------+--------+------+------------+---
------+
| Table | Non_unique | Key_name | Seq_in_index | Co
lumn_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Co
mment |
+----------------------------+------------+------------------+--------------+---
----------+-----------+-------------+----------+--------+------+------------+---
------+
| tb_travelitinerary_archive | 0 | PRIMARY | 1 | IT
INERARYID | A | 349 | NULL | NULL | | BTREE |
|
| tb_travelitinerary_archive | 1 | COUNTRYCODE | 1 | CO
UNTRYCODE | A | 2 | NULL | NULL | | BTREE |
|
| tb_travelitinerary_archive | 1 | COUNTRYCODE | 2 | ST
ATECODE | A | 2 | NULL | NULL | YES | BTREE |
|
| tb_travelitinerary_archive | 1 | COUNTRYCODE | 3 | CI
TYCODE | A | 2 | NULL | NULL | YES | BTREE |
|

EXPLAIN SQL :

-+--------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows
| Extra |
+----+--------------------+------------------+--------+-------------------------
-+---------------+---------+----------------------------------------------+-----
-+--------------------------+
| 1 | PRIMARY | I | ALL | NULL
| NULL | NULL | NULL | 349
| |
| 1 | PRIMARY | country | eq_ref | PRIMARY,INDX_countrycode
| PRIMARY | 11 | eregister.I.COUNTRYCODE | 1
| |
| 1 | PRIMARY | state | ref | PRIMARY
| PRIMARY | 302 | eregister.I.STATECODE | 1
| |
| 1 | PRIMARY | city | ref | PRIMARY
| PRIMARY | 302 | eregister.I.CITYCODE | 1
| |
| 1 | PRIMARY | R | eq_ref | PRIMARY
| PRIMARY | 4 | eregister.I.REGID | 1
| |
| 1 | PRIMARY | T | ref | PRIMARY
| PRIMARY | 4 | eregister.R.REGID | 1
| |
| 1 | PRIMARY | N | eq_ref | PRIMARY
| PRIMARY | 32 | eregister.R.NATUREOFTRAVEL | 1
| |
| 1 | PRIMARY | O | ref | PRIMARY
| PRIMARY | 313 | eregister.I.COUNTRYCODE,eregister.I.CITYCODE | 1
| Using index |
| 1 | PRIMARY | mfawebportal_om | eq_ref | PRIMARY
| PRIMARY | 32 | eregister.O.OMCODE | 1
| |
| 1 | PRIMARY | WM | ref | PRIMARY
| PRIMARY | 302 | func | 1
| Using index |
| 1 | PRIMARY | ZM | ref | INDX_citycode
| INDX_citycode | 302 | func | 1
| Using index |
| 1 | PRIMARY | Z | ALL | PRIMARY
| NULL | NULL | NULL | 1
| |
| 1 | PRIMARY | CZ | eq_ref | PRIMARY,ZONECODE
| PRIMARY | 364 | func,eregister.Z.ZONECODE | 1
| Using index |
| 3 | DEPENDENT SUBQUERY | mfawebportal_om | eq_ref | PRIMARY
| PRIMARY | 32 | func | 1
| Using where |
| 4 | DEPENDENT SUBQUERY | tb_omcitymapping | ref | PRIMARY
| PRIMARY | 313 | eregister.I.COUNTRYCODE,const | 1
| Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tb_omcitymapping | ref | PRIMARY
| PRIMARY | 313 | eregister.I.COUNTRYCODE,const | 1
| Using where; Using index |
+----+--------------------+------------------+--------+-------------------------
-+---------------+---------+----------------------------------------------+-----
-+--------------------------+
16 rows in set (0.01 sec)



Edited 1 time(s). Last edit at 06/16/2010 12:44AM by Yeo Wee tat.