long Mysql query time and high resource usage

  • behinam

    (@behinam)


    4 years, 8 months ago

    my website run this query for long time (when is see show full processlist:

     1045615 | user_upp | localhost | user_upp | Query   |  1105| Storing result in query cache | SELECT postmeta.post_id FROM wp_bij_postmeta as postmeta
                                LEFT JOIN wp_bij_postmeta as postmeta_2 ON postmeta.post_id = postmeta_2.post_id
                                LEFT JOIN wp_bij_postmeta as postmeta_3 ON postmeta.post_id = postmeta_3.post_id
                                WHERE postmeta.meta_key = '_sale_price_dates_from'
                                        AND postmeta_2.meta_key = '_price'
                                        AND postmeta_3.meta_key = '_sale_price'
                                        AND postmeta.meta_value > 0
                                        AND postmeta.meta_value < '1566747377'
                                        AND postmeta_2.meta_value != postmeta_3.meta_value |    0.000 |
| 1046598 | root         | localhost | NULL         | Query   |    0 | Init                          | show full processlist

    and server resource usage is high, how can i solved this issue?

    • This topic was modified 4 years, 8 months ago by behinam.
Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator t-p

    (@t-p)

    4 years, 8 months ago

    Slowness/high CPU usage could be due to (but not limited to):
    – A poor or memory intensive theme. Switch to the WordPress default theme and test loading time and see if you see a difference. If you do, may be your theme can be re-written to be more efficient.
    – Poor or memory intensive plugins. One by one disable plugins and test loading time and see if you see a difference. If you do, may be that plugin can be re-written to be more efficient.
    – your server is slow (e.g., “process hang”, overload, you get a lot of visitors, other problems, etc.); generally, every inexpensive host out there is slow.
    – I don’t know if you use CloudFlare or not but if you do I would suggest deactivating it and seeing if it helps. Also sometimes a good purge of the cache on CloudFlare can help.
    – Lots of images can cause slowness. Also images loaded without compression.
    – Lots of external content (facebook/ gravatar/ google adds, etc.) and redirects in general tends to slow sites down.

    Review these help guides with some useful optimization tips:

    Optimization


    https://www.wpbeginner.com/wordpress-performance-speed/
    https://wpengine.com/blog/site-speed-make-wordpress-lightning-fast/

    Moderator bcworkz

    (@bcworkz)

    4 years, 8 months ago

    Complex meta queries are inherently inefficient. On modest sites, the inefficiency usually isn’t so noticeable. With very large meta tables it becomes obvious. Using a more powerful, faster server will help some, but does not change the inefficiency. Placing meta data that is frequently queried into it’s own custom table where each item that currently has its own meta key is placed in its own column will be your best option. Especially if the column can be indexed.

    Obviously this is a major data restructure. For any decent amount of data, you’ll need a custom script to move the data. And of course any current code that uses get_post_meta() for such data will need to be rewritten.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘long Mysql query time and high resource usage’ is closed to new replies.