Title: slow database query
Last modified: September 28, 2018

---

# slow database query

 *  [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/)
 * I found this sql cause the site slow down and mysql database has more then 800
   process.
 * SELECT DISTINCT
    ID, postnumber, cntaccess AS sum_count FROM wp_top_ten INNER
   JOIN wp_posts ON postnumber = ID WHERE 1 = 1 AND blog_id = 1 AND ( wp_posts.post_status
   = ‘publish’ OR wp_posts.post_status = ‘inherit’ ) AND wp_posts.post_type IN (‘
   post’) ORDER BY sum_count DESC LIMIT 0, 50
    -  This topic was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).

Viewing 11 replies - 1 through 11 (of 11 total)

 *  Plugin Author [Ajay](https://wordpress.org/support/users/ajay/)
 * (@ajay)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10735395)
 * Can you please check if there are the PRIMARY KEYS? You can try to use the button
   in the Tools page to regenerate these. It sometimes doesn’t work on some sites
   and then you’ll need to use the below code but change wp_ if you have a custom
   prefix for the tables.
 *     ```
       ALTER TABLE wp_top_ten DROP PRIMARY KEY;
       ALTER TABLE wp_top_ten_daily DROP PRIMARY KEY;
       ALTER TABLE wp_top_ten ADD PRIMARY KEY(postnumber, blog_id);
       ALTER TABLE wp_top_ten_daily ADD PRIMARY KEY(postnumber, dp_date, blog_id); 
       ```
   
 * Always backup your database before doing any major changes just to be safe.
 *  Thread Starter [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10747914)
 * I recreated the index and i will observed it for a while.Thanks for you help
 *  Thread Starter [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10752917)
 * Hello, after a few days, I found the query is also very slow.
    below is the query
   explain. explain SELECT DISTINCT ID, postnumber, SUM(cntaccess) as sum_count 
   FROM wp_top_ten_daily INNER JOIN wp_posts ON postnumber=ID WHERE 1=1 AND blog_id
   = 1 AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘inherit’)
   AND dp_date >= ‘2018-09-29 0’ AND wp_posts.post_type IN (‘post’) GROUP BY postnumber
   ORDER BY sum_count DESC LIMIT 0, 50
 * +——+————-+——————+——–+———————————————-+———+———+——————————————–+———+———————————————-
   +
    | id | select_type | table | type | possible_keys | key | key_len | ref | 
   rows | Extra | +——+————-+——————+——–+———————————————-+———+———+——————————————–+———
   +———————————————-+ | 1 | SIMPLE | wp_top_ten_daily | index | PRIMARY | PRIMARY
   | 21 | NULL | 1686564 | Using where; Using temporary; Using filesort | | 1 | 
   SIMPLE | wp_posts | eq_ref | PRIMARY,bk2,wp_posts_idx_type_status_date_id | PRIMARY
   | 8 | wordpress.wp_top_ten_daily.postnumber | 1 | Using where | +——+————-+——————
   +——–+———————————————-+———+———+——————————————–+———+———————————————-+
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
 *  Thread Starter [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10753000)
 * i found there are many same sql at same time. my cache time is hour. i think 
   after the cache has expired, too many sql running at same time cause database
   slow.
 * SELECT DISTINCT ID, postnumber, SUM(cntaccess) as sum_count FROM wp_top_ten_daily
   INNER JOIN wp_posts ON postnumber=ID WHERE 1=1 AND blog_id = 1 AND (wp_posts.
   post_status = ‘publish’ OR wp_posts.post_status = ‘inherit’) AND dp_date >= ‘
   2018-10-05 0’ AND wp_posts.post_type IN (‘post’) GROUP BY postnumber ORDER BY
   sum_count DESC LIMIT 0, 50
 *  Thread Starter [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10754233)
 * I think the widgets should always read data from the cache, and every time the
   cache time is up, refresh the cache，when the refresh cache process is running,
   the widgets shoud use old cache data so even if it runs very slowly, it won’t
   cause database slow.
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
 *  Plugin Author [Ajay](https://wordpress.org/support/users/ajay/)
 * (@ajay)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10755442)
 * Thanks for the detailed checking. If you’ve enabled the cache from the plugins
   page, the widget uses the transienst api to cache the posts output and then refreshes
   it automatically.
 * One thing I notice is that you said there are multiple queries which confuses
   me. Do you have different widgets on the page from Top 10? Or, is it the single
   one generating this?
 * Also, noticed the huge size of the wp_top_ten_daily table. Do you have the maintenance
   enabled to clean up this table at regular intervals?
 * [https://ps.w.org/top-10/assets/screenshot-6.png?rev=1770116](https://ps.w.org/top-10/assets/screenshot-6.png?rev=1770116)
 *  Thread Starter [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10757602)
 * I really have multiple widget using different period range.
 * But i found the same sql is totally same,if the multiple sql is made from diferrent
   widget ,the time in the sql should be different .
 * `AND dp_date >= ‘2018-10-05 0’`
 * I think that means when the cache expire but not generated complete because of
   the slow database query.And then the next requests is coming.So many query directly
   made so many sql,so the database have many process to running these sql,that 
   will cause database high load.After the first query is complete,the cache has
   been generate. And after the cache generated,widget will read from the cache 
   and database work well.
 * And huge size of the wp_top_ten_daily table is because i have nearly 40 million
   posts,i have no idea to reduce it
 * my setting is in the image.
 * [https://i.postimg.cc/TwQtb6Wr/image.jpg](https://i.postimg.cc/TwQtb6Wr/image.jpg)
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
 *  Thread Starter [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10757697)
 * Because of my poor english.Maybe I can’t express my meaning very well.
    But i
   found a project may said what i want to said…..
 * [https://github.com/10up/Async-Transients](https://github.com/10up/Async-Transients)
 * That its said is my thought….
    [https://i.postimg.cc/28qmm4y7/image.png](https://i.postimg.cc/28qmm4y7/image.png)
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
    -  This reply was modified 7 years, 8 months ago by [kiwibird](https://wordpress.org/support/users/kiwibird/).
 *  Thread Starter [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10764370)
 * After testing for a week.I found this plugin is useful but when runinng on a 
   large site.The sql its made is a big performance issue can cause database down
   again and again.So i give up this plugin. Thanks for yuor amazing work.
 *  Plugin Author [Ajay](https://wordpress.org/support/users/ajay/)
 * (@ajay)
 * [7 years, 7 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10778558)
 * Thanks for the detailed testing. I’ll take a look at the aync transients. There
   is clearly more to do from the testing perspective for larger sites particularly
   around the caching.
 *  Thread Starter [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * (@kiwibird)
 * [7 years, 7 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10779409)
 * Thank you for your reply, I am looking forward to this useful plugin getting 
   better and better.

Viewing 11 replies - 1 through 11 (of 11 total)

The topic ‘slow database query’ is closed to new replies.

 * ![](https://ps.w.org/top-10/assets/icon-256x256.png?rev=2986432)
 * [WebberZone Top 10 — Popular Posts](https://wordpress.org/plugins/top-10/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/top-10/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/top-10/)
 * [Active Topics](https://wordpress.org/support/plugin/top-10/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/top-10/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/top-10/reviews/)

 * 11 replies
 * 2 participants
 * Last reply from: [kiwibird](https://wordpress.org/support/users/kiwibird/)
 * Last activity: [7 years, 7 months ago](https://wordpress.org/support/topic/slow-database-query-3/#post-10779409)
 * Status: not resolved