WordPress.org

Ready to get started?Download WordPress

Forums

Count per Day
[resolved] Slow SQL queries and bad performance (6 posts)

  1. FolioVision
    Member
    Posted 2 years ago #

    Hello,

    this plugin contains some bad SQL queries which might cause issues on your server, as they might easily take more than 1 second to execute:

    SELECT COUNT(*) FROM (SELECT 1 FROM wp_cpd_counter GROUP BY date, ip) t

    Our wp_cpd_counter database had around 500,000 entries. We removed this plugin.

    Thanks,
    Martin

    http://wordpress.org/extend/plugins/count-per-day/

  2. Tom Braider
    Member
    Plugin Author

    Posted 1 year ago #

    Why you don't use the function to collect older data and speed up the queries?
    It's created for bigger site like yours.

  3. FolioVision
    Member
    Posted 1 year ago #

    Hello Tom,

    I just installed this plugin and I could see that the above bad query is running on Dashboard. You should really fix that if you already haven't.

    Thanks,
    Martin

  4. Tom Braider
    Member
    Plugin Author

    Posted 1 year ago #

    This query takes on my test DB with near 1.000.000 entries 0.4 seconds. I think, thats OK.
    Maybe you have to check your (mysql) server.
    Otherwise tell me how to optimize this query. I don't know.

  5. FolioVision
    Member
    Posted 1 year ago #

    Hello Tom,

    I'm not sure, but you should be able to use just a single SQL select and use the COUNT function in it. Then you should run EXPLAIN on the query and figure out what indexing key should be added.

    I can see that there are some keys already. Perhaps we initially installed an older version of this plugin and then the upgrade didn't created the keys, if you added them later.

    Thanks,
    Martin

  6. Tom Braider
    Member
    Plugin Author

    Posted 1 year ago #

    Hi Martin,

    that's my index: http://gyazo.com/4f4993edbaf86fc8f8c9b9ecd964acbb
    You can compare it but i think you have the same.
    The "problem" is, i have to group the visitors first and count than the rows. Otherwise i will get the reads not the visitors per day. That's the reason of the two queries.

    I could just get the reads and count them per PHP. But this would need much more PHP-Memory because i would get much more data from the mysql server. It's the better way to let do mysql much as possible.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic