MySQL Forums :: Newbie :: Slow queries with very large table


Advanced Search

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 Enrique Rochina 01/07/2014 04:12AM
Re: Slow queries with very large table Peter Brawley 01/07/2014 12:16PM
Re: Slow queries with very large table Rick James 01/07/2014 01:04PM
Re: Slow queries with very large table Enrique Rochina 01/08/2014 06:10AM
Re: Slow queries with very large table Rick James 01/08/2014 11:58AM
Re: Slow queries with very large table Enrique Rochina 01/14/2014 10:39AM
Re: Slow queries with very large table Rick James 01/14/2014 10:57AM
Re: Slow queries with very large table Enrique Rochina 01/15/2014 06:18AM
Re: Slow queries with very large table Rick James 01/15/2014 11:09AM
Re: Slow queries with very large table Enrique Rochina 01/16/2014 02:47AM
Re: Slow queries with very large table Rick James 01/16/2014 04:46PM


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.