4
\$\begingroup\$

I've got the below SQL statement which runs in MySQL on a WordPress installation. I'm looking for meta data that matches certain criteria and was created after a certain date.

However, WordPress doesn't store the date that metadata was added to posts, so I've created the metadata itself (wpcf-post-likes), the date it was added (wpcf-post-like-date) and a third piece of metadata which is created at the same time that contains the IDs of the previous two delimited by an exclamation mark (wpcf-post-like-date-link).

I then use the following SQL statement to get likes on posts written by the current author (example ID included in the SQL code below) after a certain date. It works, but it takes about 7 - 8 seconds to run, which is far from ideal. Is there a more efficient version of the same statement?

SELECT `meta_id` FROM `wp_postmeta` 
WHERE   `post_id` IN (
            SELECT `ID` FROM `wp_posts` WHERE post_author = $user_id
        ) AND
        `meta_value` IN (

        SELECT CONCAT(combined_ids_a.`meta_id`, '!', combined_ids_b.`meta_id`) AS `combined_meta_id` FROM (

            SELECT `meta_id`, 'like_id' AS `meta_type` FROM `wp_postmeta` 
            WHERE `meta_key` LIKE 'wpcf-post-likes'
            AND `meta_value` NOT LIKE '1837'
            AND `meta_id` IN 
            (SELECT SUBSTRING_INDEX(`meta_value`, '!', 1) FROM `wp_postmeta` WHERE `meta_key` LIKE 'wpcf-post-like-date-link' ORDER BY `meta_id` DESC)

            UNION

            SELECT  `meta_id`, 'like_date' AS `meta_type`  FROM `wp_postmeta` 
            WHERE `meta_key` LIKE 'wpcf-post-like-date'
            AND `meta_value` > '01-02-2016 09:20:34'
            AND `meta_id` IN 
            (SELECT SUBSTRING_INDEX(`meta_value`, '!', -1) FROM `wp_postmeta` WHERE `meta_key` LIKE 'wpcf-post-like-date-link' ORDER BY `meta_id` DESC)

        ) AS combined_ids_a JOIN 

        (

            SELECT `meta_id`, 'like_id' AS `meta_type` FROM `wp_postmeta` 
            WHERE `meta_key` LIKE 'wpcf-post-likes'
            AND `meta_value` NOT LIKE '1837'
            AND `meta_id` IN 
            (SELECT SUBSTRING_INDEX(`meta_value`, '!', 1) FROM `wp_postmeta` WHERE `meta_key` LIKE 'wpcf-post-like-date-link' ORDER BY `meta_id` DESC)

            UNION

            SELECT  `meta_id`, 'like_date' AS `meta_type`  FROM `wp_postmeta` 
            WHERE `meta_key` LIKE 'wpcf-post-like-date'
            AND `meta_value` > '01-02-2016 09:20:34'
            AND `meta_id` IN 
            (SELECT SUBSTRING_INDEX(`meta_value`, '!', -1) FROM `wp_postmeta` WHERE `meta_key` LIKE 'wpcf-post-like-date-link' ORDER BY `meta_id` DESC)

        ) AS combined_ids_b    

        );
\$\endgroup\$
1
\$\begingroup\$

Suggest staring with this:

AND `meta_id` IN 
        (SELECT SUBSTRING_INDEX(`meta_value`, '!', 1) 
         FROM `wp_postmeta` 
         WHERE `meta_key` 
         LIKE 'wpcf-post-like-date-link' 
         ORDER BY `meta_id` 
         DESC)

LIKE text searching, sorting with the ORDER, additional subquery on the same wp_postmeta table are sources of the lengthy query execution. Rewriting subqueries as joins

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.