slower queries after partitioning

[Mat private]

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