MySQL Forums
Forum List  »  Optimizer & Parser

Re: index not used when join condition added
Posted by: Rick James
Date: January 04, 2012 06:26PM

They are doing the "right thing".
explain 
select  count(*)
    from  eodxmlperiods, idfromsymbol
    where  eodxmlperiods.datetime BETWEEN '2011-12-12 01:00:00'
      AND  '2011-12-12 23:00:00'
      AND  eodxmlperiods.symbolid=idfromsymbol.id
      AND  idfromsymbol.exchange='AMEX';
select  count(*)
    from  eodxmlperiods
    where  eodxmlperiods.datetime BETWEEN '2011-12-12 01:00:00'
      AND  '2011-12-12 23:00:00'
The COUNT(*) said "Using index" and "range". And it estimated hitting 2M of the 11M rows. "Using index" means that all the necessary fields can be found in the index (KEY `eodxmlperiods1` (`datetime`)), so it did not have to touch the data.

The first query is more complicated. If it were to start with the index, it would be reaching from the index to the data (2M times) to find eodxmlperiods.symbolid. Then another 2M times (1 per row found so far) to reach into the other table.

Instead of that 2-step (index, then data), it decided it would be more efficient (actually much more efficient in your case), to scan all 11M rows in the data, ignoring the index.

Doing this would let that query also be "Using index":
ALTER TABLE eodxmlperiods
DROP INDEX eodxmlperiods1,
ADD INDEX (`datetime`, symbolid);
This would slow down the COUNT(*) a tiny bit.

A side note: InnoDB must have a PRIMARY KEY. You did not provide one, so it made up a hidden 6-byte integer. It would be better to have your own. Perhaps one of these:
PRIMARY KEY(`datetime`, symbolid), or
PRIMARY KEY(symbolid, `datetime`)
If you do the former, then my previous INDEX suggestion is redundant.

If that combination is not unique (which a PRIMARY KEY must be), then
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
(assuming it will never have more than 4 billion rows).

If this is intra-day stock quotes, then you are just beginning to understand what it means to "scale". Suggest you squeeze out a little more space by making symbolid MEDIUMINT UNSIGNED NOT NULL. That will save 1 byte per row (in each table). This assumes you will not have more than 16M tickers.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: index not used when join condition added
1301
January 04, 2012 06:26PM


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.