1.11.0: slow search queries

[donmhico]

Description

This PR vastly improves the loading times when searching the logs. It achieves this by adding FULL TEXT INDEX in the message column and using MATCH AGAINST which utilizes the FULL TEXT INDEX instead of using LIKE in the search query.

This DB upgrade is not performed automatically (there's a UI/button that the user needs to click) to give the user the chance to secure a backup before we alter the DB. Adding a FULL TEXT INDEX may take a lot of time and resources depending on how large the existing DB table (the more the logs are, the longer it will take). This problem will be apparent to WP sites running in a shared web hosting environment where resources are limited.

To address this issue, the DB upgrade process will delete all existing email logs before altering the DB to add the FULL TEXT INDEX.

The DB upgrade process also includes #173. That process runs quickly so we've decided to run them together in a single DB Upgrade button click (single admin interaction) instead of requiring the admin to manually trigger the DB upgrade twice.

Important note

This upgrade is totally optional for existing users. The search function will still work even if the user does not upgrade. Existing users can also dismiss the DB upgrade notice and it won't show up again.

We will be publishing a setup-by-setup tutorial on how to perform the DB upgrade manually for our existing users who doesn't want to lose all existing mail logs.

Motivation

Fixes #154.

Testing procedure

We test this PR in 2 user stories:

A. Fresh install

1. Activate the plugin.
2. Check your database. You can easily do this if you have phpMyAdmin. Navigate to wp_wpml_mails. Click "Show Structure", and you should see FULLTEXT | message.

3. Navigate to your WordPress Dashboard -> WP Mail Logging -> Settings. You should not see the database upgrade UI. You should only see the Settings page as usual.
4. Try to send emails (to save new email logs).
5. Confirm that searching by "Message" works.

B. Plugin update (existing users)

1. Navigate to your WordPress Dashboard -> WP Mail Logging. In both "Email Logs" and "SMTP" pages, you should see an admin warning notice regarding the database upgrade. And clicking the link, should redirect you to the "Settings" page.

2. In the "Settings" page, you should see a Database upgrade section.

3. Click the "Upgrade" button.
4. Confirm that you want to perform the upgrade.

5. You should see an admin notice once the database upgrade is done.

6. Check your database. You can easily do this if you have phpMyAdmin. Navigate to wp_wpml_mails. Click "Show Structure", and you should see FULLTEXT | message.
7. Confirm that searching by "Message" still works and work much faster.

[lkraav]

To address this issue, the DB upgrade process will delete all existing email logs except for the most recent 500 before altering the DB to add the FULL TEXT INDEX.

I for one wouldn't be OK with having customer outbound email history deleted, it's valuable for customer success agents.

What can we do to make deletion optional?

[donmhico]

@lkraav - Thank you for your comment. This DB upgrade is totally optional. We will be publishing a step-by-step tutorial soon for existing users to also perform the upgrade without losing their existing logs.

[lkraav]

Thank you for your comment. This DB upgrade is totally optional. We will be publishing a step-by-step tutorial soon for existing users to also perform the upgrade without losing their existing logs.

Thanks. I hope testing is solid here, losing this email data because of some random conditional bug would hurt.

[donmhico]

@lkraav - We've released the latest version - 1.12.0. Please follow the guide here to manually perform the DB upgrade and preserve your existing email logs.

Thank you for your comments here! 🙇

[No3x]

@donmhico, just wanted to let you know that the manual upgrade using the guide worked like a charm for me. Thanks a lot for putting in the effort!