Title: Database optimizations
Last modified: January 28, 2023

---

# Database optimizations

 *  Resolved [DSmidge](https://wordpress.org/support/users/dsmidgy/)
 * (@dsmidgy)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/)
 * 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 valueALTER 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](https://wordpress.org/support/users/dsmidgy/)
 * (@dsmidgy)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16442102)
 * 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](https://wordpress.org/support/users/warfareplugins/)
 * (@warfareplugins)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16470912)
 * Hi [@dsmidgy](https://wordpress.org/support/users/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](https://github.com/warfare-plugins/social-warfare)
 *  Thread Starter [DSmidge](https://wordpress.org/support/users/dsmidgy/)
 * (@dsmidgy)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16471024)
 * Ok. I made a fork and I will try to fix those things.
 *  Thread Starter [DSmidge](https://wordpress.org/support/users/dsmidgy/)
 * (@dsmidgy)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16471218)
 * 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](https://wordpress.org/support/users/warfareplugins/)
 * (@warfareplugins)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16471287)
 * Perfect. Thank you [@dsmidgy](https://wordpress.org/support/users/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](https://wordpress.org/support/users/dsmidgy/)
 * (@dsmidgy)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16471571)
 * 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](https://wordpress.org/support/users/dsmidgy/)
 * (@dsmidgy)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16471603)
 * 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](https://wordpress.org/support/users/dsmidgy/)
 * (@dsmidgy)
 * [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16474789)
 * 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.

 * ![](https://ps.w.org/social-warfare/assets/icon-256x256.jpg?rev=2272097)
 * [Social Sharing Plugin - Social Warfare](https://wordpress.org/plugins/social-warfare/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/social-warfare/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/social-warfare/)
 * [Active Topics](https://wordpress.org/support/plugin/social-warfare/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/social-warfare/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/social-warfare/reviews/)

 * 8 replies
 * 2 participants
 * Last reply from: [DSmidge](https://wordpress.org/support/users/dsmidgy/)
 * Last activity: [3 years, 3 months ago](https://wordpress.org/support/topic/database-optimizations/#post-16474789)
 * Status: resolved