Database optimizations

  • Resolved DSmidge

    (@dsmidgy)


    1 year, 10 months ago

    Hi,

    I noticed some problems with database queries on my server and I am writing how I fixed them. The blog I am managing has lots of posts.

    These queries were reported as slow by slow logger in MySQL.

    UPDATE wp_swp_analytics SET post_id = 0, date = ‘2023-01-27’, facebook = ‘10659’, pinterest = ‘1235’, total_shares = ‘11894’ WHERE post_id = 0 AND date = ‘2023-01-27’ — These can be removed from SET: post_id = 0, date = ‘2023-01-27’,

    SELECT * FROM wp_3_swp_analytics WHERE post_id = 18980 && date = ‘2023-01-27’ — Word “AND” should be used instead of deprecated “&&”.

    The slow queries where fixed by creating an index:
    ALTER TABLE wp_swp_analytics CHANGE date date DATE NOT NULL DEFAULT ‘0001-01-01’; — 0000-00-00 is not a supported value
    ALTER TABLE wp_swp_analytics ADD INDEX post_id__date (post_id, date); — Can be made date__post_id is both columns are always included in the WHERE filter in query
    — ALTER TABLE wp_swp_analytics ADD UNIQUE post_id__date (post_id, date); — This did not work because multiple rows exists for certain post_id and date; I do not think this is OK. “INSERT INTO … ON DUPLICATE KEY UPDATE …” can be used here. If ID is generated, than it can be removed and date+post_id can be defined as a primary key.

    Best regards,
    D.

Viewing 8 replies - 1 through 8 (of 8 total)
  • Thread Starter DSmidge

    (@dsmidgy)

    And another thing I noticed – lots of records are present in wp_swp_analytics table where all values (including total_shares) are 0 – in my case over 700k rows. Shouldn’t it be better not to write these rows into the table?

    Plugin Author WarfarePlugins

    (@warfareplugins)

    Hi @dsmidgy these are great ideas. Are you on GitHub? We have a list of bug reports, requested features, and maintenance topics. Do you mind going there and sharing your ideas. We love the feedback https://github.com/warfare-plugins/social-warfare

    Thread Starter DSmidge

    (@dsmidgy)

    Ok. I made a fork and I will try to fix those things.

    Thread Starter DSmidge

    (@dsmidgy)

    I see that the plugin that my wife uses is actually the PRO version. I haven’t found references to wp_swp_analytics in free version.

    Plugin Author WarfarePlugins

    (@warfareplugins)

    Perfect. Thank you @dsmidgy for the feedback and I saw your fork in the Git. the developers will review your fork

    It is great to have your feedback and support. Much appreciated

    Thread Starter DSmidge

    (@dsmidgy)

    The code is pushed into my fork. Be aware that the changes are not tested as I only have a production environment.

    Because data with zeros will be removed from wp_swp_analytics table, the code for the graph should be checked.

    Thread Starter DSmidge

    (@dsmidgy)

    In know REPLACE instead of INSERT/UPDATE is not the best option. If possible, INSERT ON DUPLICATE UPDATE should be used (but no WP command exists for this, so it must be written manually).

    Thread Starter DSmidge

    (@dsmidgy)

    I replaced the REPLACE with INSERT ON DUPLICATE UPDATE. But is not tested and there may be errors in the code as I am not a PHP developer.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Database optimizations’ is closed to new replies.