MySQL Forums
Forum List  »  Optimizer & Parser

Re: another problem with index
Posted by: Rick James
Date: July 07, 2011 09:53PM

CREATE TABLE `date` (
`data_Id` int(10) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`data_Id`),
KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I can think of no reason to have such a table. It may sound like the "right thing to do" according to Normalization textbooks, but it is not. Get rid of it. Replace data_Id by date in the other table(s). It will shrink the other tables because DATE is 3 bytes (note the key_len in the EXPLAIN); INT is 4.

Once you have recovered from the abuse I just dealt you, let me tell you better news. Your SELECT won't need to JOIN, and it will run _much_ faster because it won't have to bounce between the two tables.

As for your specific question:
> I was expecting the 93 to be a 4, because the condition rop >=1 and rop <=4.
The 93 is an estimate; the optimizer does not work very hard at deriving that number. It probably derived the 93 without looking at the rop range.

Options: ReplyQuote


Subject
Views
Written By
Posted
2341
July 06, 2011 08:39AM
Re: another problem with index
1209
July 07, 2011 09:53PM
1009
July 08, 2011 02:42AM


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.