Question 383902 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress and MariaDB: Sorting (internally using inner join) doesn't work?

I use WAMP on my dev machine running MariaDB. On the server I use MySQL. The main page is a list of events sorted by a metavalue: event_date. Here’s the query as generated by WordPress

SELECT   wp_posts.ID, wp_postmeta.meta_value
                    FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
                    WHERE 1=1  AND ( 
  wp_postmeta.meta_key = 'event_date_time' 
  AND 
  ( 
    ( 
      ( mt1.meta_key = 'event_date_time' AND CAST(mt1.meta_value AS DATE) >= '2024-04-09' )
    )
  )
) AND wp_posts.post_type = 'tc_events' AND ((wp_posts.post_status = 'publish'))
                    GROUP BY wp_posts.ID
                    ORDER BY wp_postmeta.meta_value ASC
                    LIMIT 0, 200;

This is vanilla WordPress WP_Query using args.
Works fine on the server (Ubuntu/Apache/MySQL). But on the laptop, running MariaDB, the Order By is ignored. Poking around the intarwebs, seems like MariaDB adheres to SQL standards more rigorously and there are issues with Order By. But everything on the very same intarwebs says that MariaDB is just fine for WordPress?

Tags:

2

Answers


  1. 0

    Technically WordPress should work fine with MariaDB, and the query should have produced the same result on both MySql and MariaDB. you would need to verify if there is any version conflict https://make.wordpress.org/hosting/handbook/compatibility/#wordpress-php-mysql-mariadb-versions

    MariaDB tends to be stricter with queries using GROUP BY and ORDER BY together. In MariaDB, you need to include all non-aggregated columns from the SELECT clause in the GROUP BY clause.
    So try changing your GROUP BY clause

    GROUP BY wp_posts.ID,wp_postmeta.meta_value
    Login or Signup to reply.
  2. 0

    The query JOINs to wp_postmeta twice. The one to use in ORDER BY is ambiguous. The following may fix that and numerous other issues:

    SELECT  wp_posts.ID, mt1.meta_value
    FROM  wp_posts
    INNER JOIN  wp_postmeta AS mt1  ON ( wp_posts.ID = mt1.post_id )
    WHERE  mt1.meta_key = 'event_date_time'
      AND  mt1.meta_value >= '2024-04-09'
      AND  wp_posts.post_type = 'tc_events'
      AND  wp_posts.post_status = 'publish'
    ORDER BY  mt1.meta_value ASC
    LIMIT  0, 200;

    Notes:

    • The GROUP BY seems useless (or maybe incorrect)
    • This assumes that dates are stored in mt1 in syntax like ‘2024-04-09…’
    • This plugin should help with performance: WP Index Improvements
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search