MySQL Forums
Forum List  »  Partitioning

slower queries after partitioning
Posted by: Mat private
Date: October 21, 2010 11:46AM

Hi,

I've added a thread to the innoDB forum, but I think it's better of here.
Sorry afor the cross post.

Hi,

I’ve got a table that had this structure:

CREATE TABLE `quotes` (
`DBDate` int(11) unsigned NOT NULL,
`TickerID` int(11) unsigned NOT NULL,
`Open` double(12,4) NOT NULL,
`High` double(12,4) DEFAULT NULL,
`Low` double(12,4) DEFAULT NULL,
`Close` double(12,4) DEFAULT NULL,
`AdjClose` double(12,4) DEFAULT NULL,
`Volume` int(11) unsigned NOT NULL,
PRIMARY KEY (`DBDate`,`TickerID`),
KEY `TickerID` (`TickerID`),
CONSTRAINT `Quotes_fk_TickerID1` FOREIGN KEY (`TickerID`) REFERENCES `tickers` (`TickerID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

I’ve copied the table, to do some testing.
After these queries:

ALTER TABLE quotes_copy DROP KEY `TickerID`;

ALTER TABLE quotes_copy
ADD KEY `TickerID` (`TickerID`)
partition BY Key(`TickerID`)
partitions 250;

the copied table has this structure

CREATE TABLE `quotes_copy` (
`DBDate` int(11) unsigned NOT NULL,
`TickerID` int(11) unsigned NOT NULL,
`Open` double(12,4) NOT NULL,
`High` double(12,4) DEFAULT NULL,
`Low` double(12,4) DEFAULT NULL,
`Close` double(12,4) DEFAULT NULL,
`AdjClose` double(12,4) DEFAULT NULL,
`Volume` int(11) unsigned NOT NULL,
PRIMARY KEY (`DBDate`,`TickerID`),
KEY `TickerID` (`TickerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci /*!50100 PARTITION BY KEY (TickerID) PARTITIONS 250 */

The table contains about 3.178.300 rows and has 704 different TickerID’s.

But the strange thing is, the partitioned table is slower with these queries (running RESET QUERY CACHE; before every query):
select count(*) from eodstockquotes.quotes_copy;
10141 ms
select count(*) from eodstockquotes.quotes;
3453 ms

select min(DBDate) from quotes_copy;
1312 ms
select min(DBDate) from quotes;
31 ms

select a.TickerID,b.Ticker,b.Name from quotes_copy as a,tickers as b where DBDate=(select min(DBDate) from quotes) and a.TickerID=b.TickerID;
1437 ms
select a.TickerID,b.Ticker,b.Name from quotes as a,tickers as b where DBDate=(select min(DBDate) from quotes) and a.TickerID=b.TickerID;
0 ms

explain partitions select count(*) from eodstockquotes.quotes_copy;

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | quotes_copy | p0,p1,p2...etc..etc.. | index | \N | TickerID | 4 | \N | 3181205 | Using index


Why is the partitioned table so much slower? How can I speed it up?
Hope someone can help me out.
Regards,

Matthijs

btw Mysql runs on windows server 2003
Mysql version:

version 5.1.30-community
version_comment MySQL Community Server (GPL)
version_compile_machine ia32
version_compile_os Win32

Options: ReplyQuote


Subject
Views
Written By
Posted
slower queries after partitioning
6678
October 21, 2010 11:46AM
2741
October 23, 2010 01:24AM
2153
October 23, 2010 08:44AM
2438
October 23, 2010 09:43AM
1878
October 23, 2010 10:48AM
1769
October 23, 2010 10:57AM
1917
October 23, 2010 06:09PM
1685
October 24, 2010 02:30AM
1722
October 25, 2010 07:39AM
1701
October 25, 2010 08:04AM


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.