mysql left join slow 速度慢

[adama]

查询语句慢：

下面是两个句子的比较：
SELECT rit.idx, rit.rit_date, supplier.supplier, consignee.consignee, rit.employee_name, rit.voyage, voyage.sfs
FROM rit
LEFT JOIN (
voyage
) ON ( voyage.number = rit.voyage ) , supplier, consignee
WHERE supplier.idx = rit.supplier_id
AND consignee.idx = rit.consignee_id
AND rit.rit_date LIKE '%%'
AND supplier.supplier LIKE '%%'
AND supplier.country LIKE '%%'
AND consignee.consignee LIKE '%%'
AND rit.rit_status LIKE '%closed%'
ORDER BY rit.idx DESC
LIMIT 0 , 20

Query took 0.4373 sec

而
SELECT rit.idx, rit.rit_date, supplier.supplier, consignee.consignee, rit.employee_name, rit.rit_status
FROM rit
LEFT JOIN (
supplier, consignee
) ON ( supplier.idx = rit.supplier_id
AND consignee.idx = rit.consignee_id )
WHERE rit.rit_date LIKE '%%'
AND supplier.supplier LIKE '%%'
AND supplier.country LIKE '%%'
AND consignee.consignee LIKE '%%'
AND rit.rit_status LIKE '%active%'
ORDER BY rit.idx DESC
LIMIT 0 , 20
用时才 Query took 0.0012 sec

相差很大，我也不太清楚是什么地方的问题，还请高手指教一下，应该如何提高速度 或者如何优化一下语句。
谢谢。

[adama]

4张表的 结构如下 ：
-- --------------------------------------------------------
-- Table structure for table `rit`
--

CREATE TABLE `rit` (
`idx` int(10) NOT NULL auto_increment,
`rit_date` char(10) default NULL,
`supplier_id` int(10) default NULL,
`consignee_id` int(10) default NULL,
`terms` char(10) default NULL,
`no_of_pkgs` char(10) default NULL,
`weight` double default NULL,
`volume` double default NULL,
`ldm` double default NULL,
`description` char(50) default NULL,
`special_instruction` text,
`sell_rate_idx` char(100) default NULL,
`sell_rate_value` char(200) default NULL,
`sell_rate_b_idx` char(100) default NULL,
`sell_rate_b_value` char(200) default NULL,
`employee_name` char(50) default NULL,
`trailer_num` char(10) default NULL,
`driver_name` char(10) default NULL,
`voyage` char(6) default NULL,
`eta_date` date default NULL,
`rit_status` char(10) default NULL,
`currency_type` char(3) default NULL,
`agent` char(10) default NULL,
`lading` varchar(10) NOT NULL,
`rit_status_1` varchar(5) NOT NULL,
`notify` varchar(50) NOT NULL,
`for_new_monetary` int(1) default NULL,
PRIMARY KEY (`idx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `supplier`
--

CREATE TABLE `supplier` (
`idx` int(10) NOT NULL auto_increment,
`supplier` varchar(50) NOT NULL,
`country` varchar(50) NOT NULL,
`place` varchar(50) NOT NULL,
`tel` varchar(50) NOT NULL,
`fax` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`ctcperson` varchar(50) NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `consignee`
--

CREATE TABLE `consignee` (
`idx` int(10) NOT NULL auto_increment,
`consignee` varchar(50) NOT NULL,
`tel` varchar(50) NOT NULL,
`fax` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`ctcperson` varchar(50) NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- --------------------------------------------------------


--
-- Table structure for table `voyage`
--

CREATE TABLE `voyage` (
`idx` int(10) NOT NULL auto_increment,
`number` char(6) default NULL,
`profit` char(20) default NULL,
`v_status` char(10) default NULL,
`total_collected` char(10) default NULL,
`total_cost` char(10) default NULL,
`cost_name` text,
`cost_value` text,
`for_new_monetary` int(1) default NULL,
`sfs` varchar(10) NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

[凤影]

先看看

[yifuzhiming]

好有难度啊,

[wwwwb]

按什么字段索引？

[liuyann]

第一个你多了一个表　voyage　
所以没什么可变性

== 思想重于技巧 ==

[懒得去死]

不是LEFT JOIN慢。而是你的LIKE语句慢

[microphonecarl]

两段语句跑出结果肯定都不一样，怎么去比较啊

[wu_bx]

用explain 看一下。行数有可能就不一样。

like 写的用不上索引，这样写无意义。