I'm working on scanning the WordPress database from the MySQL CLI (so not using PHP to query) and getting results for expired or expiring transient pairs. The trick is the pairs have the same string following either _transient_ or _transient_timeout_ but I'm not sure how to match unknown string pairs. Here are two ways I've tried do this search which seem a bit clumsy:

This matches the last n characters of the string, which isn't an ideal method:

SELECT option_name, RIGHT(option_name, 25) AS `duplicate_string`
FROM wp_options
WHERE RIGHT(option_name, 25) 
IN (
    SELECT RIGHT(option_name, 25)
    FROM wp_options
    GROUP BY RIGHT(option_name, 25)
    HAVING COUNT(option_name) > 1
AND option_name LIKE '_transient\_%'
ORDER BY `duplicate_string`;

This method is more exact but puts the results in parallel columns:

SELECT `a`.option_name, `b`.option_name 
FROM wp_options AS `a`, wp_options AS `b`
`a`.option_name LIKE '\_transient\_%' AND
`a`.option_name NOT LIKE '\_transient\_timeout\_%' 
AND `b`.option_name = CONCAT( '_transient_timeout_',
SUBSTRING(`a`.option_name, 12));

Is there a cleaner way to do this?

Your Answer

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

Browse other questions tagged or ask your own question.