MySQL Forums
Forum List  »  Newbie

Slow queries with very large table
Posted by: Enrique Rochina
Date: January 07, 2014 04:12AM

Hello everybody,

I have a table with around 10 million rows, which I have experienced very slow execution for this query:

SELECT count(pk) where TABLE;

It took 275 seconds, after that one, I retried and only 3 seconds.

MySQL database is in 5.5.27. Here is the description of the table:


CREATE TABLE `mytable` (
`hjmpTS` bigint(20) DEFAULT NULL,
`PK` bigint(20) NOT NULL,
`TypePkString` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`aCLTS` bigint(20) DEFAULT '0',
`OwnerPkString` bigint(20) DEFAULT NULL,
`modifiedTS` datetime DEFAULT NULL,
`propTS` bigint(20) DEFAULT '0',
`p_marketsharediscount` double DEFAULT NULL,
`p_contractcode` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_rejectiontext` varchar(500) COLLATE utf8_bin DEFAULT NULL,
`p_rejected` tinyint(1) DEFAULT NULL,
`p_enddate` datetime DEFAULT NULL,
`p_buyeritemcode` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_quantityband` bigint(20) DEFAULT NULL,
`p_contract` bigint(20) DEFAULT NULL,
`p_product` bigint(20) DEFAULT NULL,
`p_prices` text COLLATE utf8_bin,
`p_startdate` datetime DEFAULT NULL,
`p_netpricecode` bigint(20) DEFAULT NULL,
`p_vattype` bigint(20) DEFAULT NULL,
`p_visible` tinyint(1) DEFAULT NULL,
`p_contractpos` int(11) DEFAULT NULL,
`p_contractlistprice` double DEFAULT NULL,
`p_checkstring` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`PK`),
KEY `contractRelIDX_11003` (`p_contract`),
KEY `startDateIdx_11003` (`p_startdate`),
KEY `endDateIdx_11003` (`p_enddate`),
KEY `productIdx_11003` (`p_product`),
KEY `contractPOSPosIDX_11003` (`p_contractpos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin


Is there any special reason having a such performance difference?

Many thanks,
Enrique



Edited 1 time(s). Last edit at 01/07/2014 04:36AM by Enrique Rochina.

Options: ReplyQuote


Subject
Written By
Posted
Slow queries with very large table
January 07, 2014 04:12AM


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.