Announcement

Announcement Module
Collapse
No announcement yet.

Slow queries with very large table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow queries with very large table

    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:

    Code:
    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? We are considering to do some partitioning in the future, but we don't think it's related. Another possibility we were thinking is compacting the table (dumping and restoring it again)

    Many thanks,
    Enrique

  • #2
    Hi,

    In InnoDB, count(*) will not work like MyISAM (row count stored in table files). So when you run count(*) on InnoDB , it will find small index of the table and then check counts and specially when you are running count on primary key, it will do full table scan as Primary key is clustered in InnoDB. That's why it's taking too much time. After first time running, its possible that data will be cache in Query cache OR buffer pool so second and third time it will be faster.

    Partitions can be the better option to divide the data in multiple tables.

    Comment


    • #3
      Hello,

      Thanks for your feedback.

      Because we are using InnoDB, I'm wondering if migrating to Percona server (either 5.5 which will be as a quick replacement, or 5.6) would mitigate partially this performance problems, as I know Percona has XtraDB which is a InnoDB engine but with great optimisations.

      Best regards,
      Enrique

      Comment

      Working...
      X