I recently faced some performance issue on a CMS based application. I can somehow fix other tables. But Im not able to optimize the table which has 40+ columns and 10 columns are having text as the data type.

Total Size of the table: 20GB

Rows: 500000+

Even select count(*) from tbl took 40sec.

Where can I look to improve its performance?

  • 3
    slow query log, enable it, show the query, show EXPLAIN {query}, SHOW CREATE TABLE {tablename} for each table in query. Select count(*) is always because it needs to examine every row. – danblack Feb 19 at 2:51

100 rows/block
500000/100 = 5000 blocks
10ms per block to read
Conclusion: about 50s to do COUNT(*). So, 40s is reasonable.

But why count the number of rows in the table? Is that really your most imporant query? Let's focus on them. Sometimes the simple addition of a composite index can greatly improve performance.

500K rows / 20GB is 'large', but not huge. We can probably help you make it more performant.

What is the value of innodb_buffer_pool_size? How much RAM? If there had been enough cache space, the query might have run in 4s.

TEXT columns, when large, are stored "off record". This implies that reaching for them may take an extra disk hits. What are some of your other 'slow' queries?

Please provide the things already asked for in the Comments.

Specific query

  PRIMARY KEY (`posts_id`),
  KEY `posts_type` (`posts_type`),
  KEY `comment_count` (`comment_count`),
  KEY `published_at` (`published_at`),
  KEY `archive_at` (`archive_at`),
  KEY `create_at` (`create_at`),
  KEY `modified_at` (`modified_at`),
  KEY `edition` (`edition`),
  KEY `hot_posts` (`hot_posts`),
  KEY `fk_writter_id` (`fk_writter_id`),
  KEY `jamil_fk_page_id__published_at` (`fk_page_id`,`published_at`),
  KEY `media_tags` (`media_tags`),
  KEY `sort_order_all` (`sort_order_all`),
  KEY `sort_order_featured` (`sort_order_featured`),
  KEY `sort_order_hot` (`sort_order_hot`),
  KEY `featured_2` (`featured`,`sort_order_featured`,`publish_status`,`posts_type`,`published_at`),
  KEY `featured` (`featured`),
  KEY `publish_status` (`publish_status`),
  KEY `sort_order_page_featured` (`sort_order_page_featured`),
  KEY `page_featured` (`page_featured`)

  `published_at` datetime NOT NULL,

  WHERE  1
       AND fk_page_id IN ( '1234' )
       AND posts_type IN ( 'home' )
       AND publish_status = 'published'
       AND posts_id > '1083775'
       AND published_at <= '2019-02-19 08:12'
ORDER  BY published_at ASC
LIMIT  0, 1;

           id: 1
  select_type: SIMPLE
        table: posts
   partitions: NULL
         type: range
possible_keys: PRIMARY,posts_type,published_at,jamil_fk_page_id__published_at,publish_status
          key: published_at
      key_len: 5
          ref: NULL

This index may help:

INDEX(publish_status, fk_page_id, posts_type, published_at)

The problem is that there are too many non-trivial things in the WHERE clause. The Optimizer punted and said "Well, I will use the published_at index in hopes of helping with LIMIT; but I will cross my fingers and hope that I find a valid row (based on the other criteria) pretty soon." But, apparently, there was not such a row very soon.

  • Hey Rick, thanks for your answer. I'm thinking to do what optimisation can I do for increasing its performance. It has 60G memory and 40GB allocated for buffer pool. Since it's in cloudsql so not able to tune inndodb parameters. And the default isolation is repeatable read, cloudsql it's not possible to change. So just thinking how can I tune this. Because one query took 1min to return limit 1. Like select few cols from tbl where some conditions and some conditions. It has index for all where clause columns. So trying to optimise it. – Bhuvanesh Feb 19 at 3:47
  • 1
    "You can't tune your way out of a performance problem". Let's see that 1m query with LIMIT 1, plus SHOW CREATE TABLE. I'll bet it has a GROUP BY or ORDER BY or is missing a suitable composite index. – Rick James Feb 19 at 3:55
  • Yeah it has order by. – Bhuvanesh Feb 19 at 4:14
  • Well, let's see it. There are some cases where an index can handle the WHERE and the ORDER BY, thereby reaching for a single row, thereby being quite fast. (By that, I mean possibly under 10ms, regardless of table size.) – Rick James Feb 19 at 4:17
  • Thanks for your comments, one more thing I missed to mention, all where clause columns are varchar, int and datetime , text columns are in select – Bhuvanesh Feb 19 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.