MySQL Forums
Forum List  »  Optimizer & Parser

Re: help me, optimize a long running query
Posted by: Rick James
Date: October 27, 2011 09:04PM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
2623
October 25, 2011 12:00AM
981
October 27, 2011 12:39AM
Re: help me, optimize a long running query
1034
October 27, 2011 09:04PM
1315
December 26, 2011 11:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.