Improvement: Install index on wp_postmeta.meta_value

  • Hello!

    With huge libraries, the plugin is unusable because of an missing index on meta_value.

    The initial task to scan 25000 attachments takes roughly 2 hours.
    Browsing the library is problematic too.
    A full reindex is normally prohibited by the php max_execution_time limit of 30 seconds.

    Example Log without index on meta_value:

    
[02-Mar-2022 11:25:52 UTC]   - in scan attachments SQL: processed:  250/25049
[02-Mar-2022 11:26:40 UTC]   - in scan attachments SQL: processed:  500/25049
[02-Mar-2022 11:27:25 UTC]   - in scan attachments SQL: processed:  750/25049
[02-Mar-2022 11:28:13 UTC]   - in scan attachments SQL: processed: 1000/25049
[02-Mar-2022 11:29:02 UTC]   - in scan attachments SQL: processed: 1250/25049
.
.
.
***aborted***

    Example Log with index on meta_value:

    
[02-Mar-2022 11:55:32 UTC]   - in scan attachments SQL: processed:  250/25049
[02-Mar-2022 11:55:33 UTC]   - in scan attachments SQL: processed:  500/25049
[02-Mar-2022 11:55:35 UTC]   - in scan attachments SQL: processed:  750/25049
[02-Mar-2022 11:55:36 UTC]   - in scan attachments SQL: processed: 1000/25049
[02-Mar-2022 11:55:37 UTC]   - in scan attachments SQL: processed: 1250/25049
.
.
.
[02-Mar-2022 11:57:13 UTC]   - in scan attachments SQL: Satz: 25000/25049
*** completed in about 3 minutes ***

    Suggestion:

    – Improve the documentation to install the following index for users with large libraries: 

    
ALTER TABLE wp_postmeta  
ADD INDEX meta_value (meta_key ASC, meta_value(255) ASC);
-- meta_value is from type long_text, the index is limited to 255 chars

    – suggest to increase the max_execution_time

  • The topic ‘Improvement: Install index on wp_postmeta.meta_value’ is closed to new replies.