MySQL Forums
Forum List  »  Performance

Re: INNODB large database performance
Posted by: Rick James
Date: February 21, 2013 11:12PM

> Can you explain why this is a red flag in your book?

Some people make PRIMARY KEYs without thinking through the ramifications of a PK being unique. Or they assume a 1-second resolution is good enough to distinguish event. Sometimes (presumably including your case), it is valid for 1-second to be sufficient. Or even 1-day -- such as a table that has "daily receipts" from a cash register.

The problem is not with InnoDB (as your title implies), but with the optimizability of the query.

Do you need "LEFT"? Are you expecting that the 'right' table may have missing rows? If not remove LEFT; that will let the optimizer rearrange the order of which table to start with.

There are about 798479 rows for the given date? It _might_ be better if `data` could be scanned first. In that case what you have is good:
PRIMARY KEY (`EntryDate`,`ChannelID`)

But, if the LEFTs are needed, it should be reversed:
PRIMARY KEY (`ChannelID`.`EntryDate`)
because you are reaching into that table after you have a ChannelID from the previous table(s).

It would be helpful to see the CREATE TABLEs for the other tables. In particular, there might need to be an index on `groups` of INDEX(channelID).

NULLs and UNIQUEs may affect the choice of order of using the tables, and hence the performance.

Options: ReplyQuote


Subject
Views
Written By
Posted
3538
February 16, 2013 03:11AM
1186
February 20, 2013 05:31PM
1040
February 21, 2013 02:51AM
Re: INNODB large database performance
1269
February 21, 2013 11:12PM
1320
February 22, 2013 03:38AM
1022
February 23, 2013 12:04AM
976
February 23, 2013 03:28PM
920
February 24, 2013 02:10PM
916
February 24, 2013 03:02PM
1046
February 25, 2013 10:39PM


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.