You have
select t.id,t.poster,t.pubDate,t.company,t.status, t.product,t.price,t.units,t.intro,
tt.cate AS type_name
from trade_lead.trade AS t,
trade_lead.trade_type AS tt,
trade_lead.trade_ft as ft
where 1
and t.pubDate >= date_format(date_add(now(), interval -7 day),'%Y-%m-%d')
and t.type = tt.cat_id
and t.id = ft.id
ORDER BY ft.s_order desc,ft.id DESC
limit 0,100;
The pubDate test could be simplified to
and t.pubDate >= date_add(now(), interval -7 day)
Add a compound index to ft (it might help):
INDEX(s_order, id)
SHOW VARIABLES LIKE 'key_buffer_size';
How much RAM do you have?
You have 4.5 GB in `trade` -- do you really have that much variation in all those strings? I suspect most of them can (and should) be normalized out. This would make the table much less bulky.
Note that the EXPLAIN estimates needing to read 585401 rows. Unless you have a lot of RAM and things are already cached, that means reading much of the 4.5GB.
Note that it has to
1. gather 585401 rows from `trade`. Note: this includes the `intro` TEXT field, which is probably quite bulky.
2. reach into each of the other two tables once each
3. sort the results
4. deliver the first 100 rows
The index I suggested may (or may not) flip the EXPLAIN by having it start with ft, find the 100 rows necessary, then reach into the other two tables only once each. Maybe.
To shrink the data --
* INT(n) for a flag or status takes 4 bytes; consider TINYINT -- only 1 byte.
* `ip_address` varchar(20) collate utf8_unicode_ci default NULL, -- change to VARBINARY(39) so you can handle IPv6. This will take a max of 1+39 bytes instead of a max of 1+3*20.
* You have at least two TEXT fields; put them into a parallel table(s) if for no other reason than to make this table less bulky.
Unless you have 1791634 different companies, it would be good to have a "company" table, and JOIN on an id. It would have several of the fields -- company, fax, contact, etc.
* cate..cate7 -- is there something magical about those names? Or is this an attempt to put an array into the table? Probably they should be in another table with 2 columns: trade_id and cate. It could have multiple rows for each trade_id.
* The first of this pair is redundant and can be dropped:
KEY `pubDate` (`pubDate`),
KEY `idx_pubDate_status_id` (`pubDate`,`status`,`id`),
* If you always use date ranges, then (`pubDate`,`status`,`id`) is useless because the optimizer will almost never get past the first field. This might be more useful: (status, pubDate)