MySQL Optimization Problem

Question

I'm having problems with a query optimization. The following query takes more than 30 seconds to get the expected result.

SELECT tbl_history.buffet_q_rating, tbl_history.cod_stock, tbl_history.bqqq_change_month, stocks.ticker, countries.country, stocks.company
FROM tbl_history
INNER JOIN stocks ON tbl_history.cod_stock = stocks.cod_stock
INNER JOIN exchange ON stocks.cod_exchange = exchange.cod_exchange
INNER JOIN countries ON exchange.cod_country = countries.cod_country
WHERE exchange.cod_country =125
AND DATE = '2011-07-25'
AND bqqq_change_month IS NOT NULL 
AND buffet_q_rating IS NOT NULL 
ORDER BY bqqq_change_month DESC
LIMIT 10

The tables are:

CREATE TABLE IF NOT EXISTS `tbl_history` (
`cod_stock` int(11) NOT NULL DEFAULT '0',
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`price` decimal(11,3) DEFAULT NULL,
`buffet_q_rating` decimal(11,4) DEFAULT NULL,
`bqqq_change_day` decimal(11,2) DEFAULT NULL,
`bqqq_change_month` decimal(11,2) DEFAULT NULL,
(...)
PRIMARY KEY (`cod_stock`,`date`),
KEY `cod_stock` (`cod_stock`),
KEY `buf_rating` (`buffet_q_rating`),
KEY `data` (`date`),
KEY `bqqq_change_month` (`bqqq_change_month`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `stocks` (
`cod_stock` int(11) NOT NULL AUTO_INCREMENT,
`cod_exchange` int(11) DEFAULT NULL,
PRIMARY KEY (`cod_stock`),
KEY `exchangestocks` (`cod_exchange`),
KEY `codstock` (`cod_stock`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

CREATE TABLE IF NOT EXISTS `exchange` (
`cod_exchange` int(11) NOT NULL AUTO_INCREMENT,
`exchange` varchar(100) DEFAULT NULL,
`cod_country` int(11) DEFAULT NULL,
PRIMARY KEY (`cod_exchange`),
KEY `countriesexchange` (`cod_country`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

CREATE TABLE IF NOT EXISTS `countries` (
`cod_country` int(11) NOT NULL AUTO_INCREMENT,
`country` varchar(100) DEFAULT NULL,
`initial_amount` double DEFAULT NULL,
PRIMARY KEY (`cod_country`),
KEY `codcountry` (`cod_country`),
KEY `country` (`country`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

The first table have more than 20 million rows, the second have 40k and the others have just a few rows (maybe 100).

Them problem seems to be the "order by" but I have no idea how to optimize it. I already tried some things searching on google/stackoverflow but I was unable to get good results Can someone give me some advice?

EDIT: Forgot the EXPLAIN result:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  countries   const   PRIMARY,codcountry  PRIMARY 4   const   1   Using temporary; Using filesort
1   SIMPLE  exchange    ref PRIMARY,countriesexchange   countriesexchange   5   const   15  Using where
1   SIMPLE  stocks  ref PRIMARY,exchangestocks,codstock exchangestocks  5   databaseName.exchange.cod_exchange  661 Using where
1   SIMPLE  tbl_history eq_ref  PRIMARY,cod_stock,buf_rating,data,bqqq_change_mont...   PRIMARY 12  v.stocks.cod_stock,const    1   Using where

UPDATE

this is the new EXPLAIN I got:

id select_type table type   possible_keys                       key        key_len ref   rows Extra |
1  SIMPLE tbl_history range monthstats                          monthstats      14 NULL  80053 Using where; Using index |
1  SIMPLE countries   ref   country                             country          4 const     1 Using index |
1  SIMPLE exchange    ref   PRIMARY,cod_country,countryexchange countryexchange  5 cons‌t     5 Using where; Using index |
1  SIMPLE stocks      ref   info4stats                          info4stats       9 databaseName.exchange.cod_exchange,d‌atabaseName.stock_...                    1 Using where; Using index |

Can you paste the explain result of this query? That will give you some insights on how mysql is using the indexes etc. — Pigol
– Pigol, Commented Aug 8, 2011 at 23:24
You could try specifying the bqqq_change_month index as DESC though I can't tell for sure if this is supported correctly yet — Phil
– Phil, Commented Aug 8, 2011 at 23:32
I would surely add a compound index on table stocks on (cod_exchange,cod_stock) — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 8, 2011 at 23:46
And i would try on table history a compound index on (bqqq_change_month, buffet_q_rating, date, cod_stock) — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 8, 2011 at 23:49
@ypercube I tried your suggestions and the first one helped. The second, I found a better order for the compound, at least gives me better execution time with (date,cod_stock,bqqq_change_month,buffet_q_rating) maybe its because I have about 800 different dates and bqqq_change_month its around 20 million different rows — Rafael
– Rafael, Commented Aug 11, 2011 at 11:10

DRapp · Accepted Answer · 2011-08-12 02:32:16Z

1

I would try to preemptively start with the Country records for 125 and work in reverse. By using a Straight_join will force the order of your query as entered...

I would also have an index on your Tbl_History table by the COD_Stock and DATE( date ). So the query will properly and efficiently match the join condition on the pre-qualified date portion of the date/time field.

SELECT STRAIGHT_JOIN
      th.buffet_q_rating, 
      th.cod_stock, 
      th.bqqq_change_month, 
      stocks.ticker, 
      c.country, 
      s.company
   FROM 
      Exchange e
         join Countries c
            on e.Cod_Country = c.Cod_Country
         join Stocks s
            on e.cod_exchange = s.cod_exchange
            join tbl_history th
               on s.cod_stock = th.cod_stock
              AND th.`Date` = '2011-07-25'
              AND th.bqqq_change_month IS NOT NULL 
              AND th.buffet_q_rating IS NOT NULL 
   WHERE
      e.Cod_Country = 125
   ORDER BY 
      th.bqqq_change_month DESC
   LIMIT 10

answered Aug 12, 2011 at 2:32

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rafael Over a year ago

As another user suggested I created some compound indexes and now I have some good results. I can get the results in 0.2 (average) seconds with the query I wrote. With yours I get almost the same result and the only difference in EXAMPLE its the order, it starts in exchange table and coutries after. With this query I keep getting the "using temporary, using file sort" and I dont know how to remove it. Any ideia how? I use this indexes: tbl_history: (cod_user,date,buffet_q_rating,bqqq_change_month) stocks: (cod_exchange,cod_stock,company,ticker) exchange: (cod_country,cod_exchange)

DRapp Over a year ago

@Rafael, I would need to see the "EXPLAIN" of my query to be sure, but you are probably getting a file sort at the join to the tbl_History because of an index not matching the criteria... I would have an index on tbl_History on the COD_STOCK, and DATE(date).

Rafael Over a year ago

This is the EXPLAIN (not "example" as I wrote last time :)):

1	SIMPLE	e	ref	PRIMARY,countriesexchange	countriesexchange	5	const	5	Using where; Using index; Using temporary; Using f...     1	SIMPLE	c	ref	country	country	4	const	1	Using index     1	SIMPLE	s	ref	PRIMARY,info4stats	info4stats	5	databaseName.e.cod_exchange	697	Using where; Using index     1	SIMPLE	th	ref	monthstats	monthstats	12	databaseName.s.cod_stock,const	1	Using where; Using index

You are saying I need a compound index on (cod_stock,DATE(date))? I cant do that in mysql

DRapp Over a year ago

Even without the DATE( date ), even a compound index with COD_STOCK, date should be better

ypercubeᵀᴹ Over a year ago

@Rafael: DRapp is right. (i missed that tbl_history.date is of datetime datatype). If you only store dates there and not times, change it into date type. If you have times too, your query should be AND DATE >= '2011-07-25' AND DATE < '2011-07-26' and the index as DRapp's suggestion.

Hendra Jaya · Accepted Answer · 2011-08-12 01:57:58Z

0

If you want to limit the result, why do you do it after you join all the table? Try to reduce the size of those big tables first (LIMIT or WHERE them) before joining them with other tables.

But you have to be sure that your original query and your modified query means the same.

Update (Sample) :

select
    tbl_user.user_id,
    tbl_group.group_name
from
    tbl_grp_user
inner join
(
    select
        tbl_user.user_id,
        tbl_user.user_name
    from
        tbl_user
    limit
        5
) as tbl_user
on
    tbl_user.user_id = tbl_grp_user.user_id
inner join
(
    select
        group_id,
        group_name
    from
        tbl_group
    where
        tbl_group.group_id > 5
) as tbl_group
on
    tbl_group.group_id = tbl_grp_user.group_id

Hopefully, query above will give you a hint

edited Aug 12, 2011 at 1:57

answered Aug 9, 2011 at 3:45

Hendra Jaya

1,62811 silver badges15 bronze badges

1 Comment

Rafael Over a year ago

I am not sure if I understood, can you make an example how to limit before getting the cod_stock needed to get the results from tbl_history?

Collectives™ on Stack Overflow

MySQL Optimization Problem

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related