mariadb - join/order by clauses cause slow query

have a small blog site, host provider runs Server version: 5.5.5-10.3.34-MariaDB-cll-lve MariaDB Server. The last weeks i noticed fetching the posts is slower, and it turns out the query now takes 6 seconds.

the in question query:

SELECT * FROM POSTS
JOIN POST_CATEGORIES on POST_CATEGORIES.CATEG_ID = POSTS.POST_CATEGORY
LEFT JOIN USERS on POSTS.POST_USERNAME = USERS.USER_USERNAME
WHERE POST_STATUS = 'ENA'
AND POST_IS_PUBLIC = 'Y'
ORDER BY POST_IS_STICKY DESC, (TIMESTAMPDIFF(SECOND, POST_CREATION_DATE, now())-(POST_LIKES-POST_DISLIKES)*3600) ASC
LIMIT 0, 12;

if i remove the order by the query time drops to 0.04 sec, but obviously it is needed for proper sorting. Same effect happens if i remove the left join to USERS, but i would like to focus on the order by clause.

Table rows:

POSTS: 62K
POST_CATEGORIES: 20
USERS: 180

Interesting finding:

There are already indexes (primary/unique/foreign key) on the tables, but not on the columns involved, for the simplicity i am not adding them here.

But i did try adding the index:

INDEX `POSTS_OPTIMIZING_INDEX` (`POST_CREATION_DATE`, `POST_LIKES`, `POST_DISLIKES`) USING BTREE,

and FORCE INDEX (POSTS_OPTIMIZING_INDEX) and saw something interesting:

on the MariaDB, literally no difference.
on mysql community server Server version: 5.7.20 MySQL Community Server (GPL) that i have home, the query duration from 6 seconds went down to 0.12sec, which is definitely acceptable.

Any suggestions on optimizing the SQL, or tuning mariadb?

EDIT

the table definitions:

POSTS:

CREATE TABLE `POSTS` (
  `POST_ID` int(11) NOT NULL AUTO_INCREMENT,
  `POST_URL_ID` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_TYPE` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_CATEGORY` int(3) DEFAULT NULL,
  `POST_IS_PUBLIC` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_IS_STICKY` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_FREE_TEXT` varchar(5000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_IMAGE_FILE` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_VIDEO_URL` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_IS_FROM_USER` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_USERNAME` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_CREATION_DATE` timestamp NOT NULL DEFAULT current_timestamp(),
  `POST_STATUS` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ENA',
  `POST_LIKES` int(5) DEFAULT 0,
  `POST_DISLIKES` int(5) DEFAULT 0,
  `POST_ADDITIONAL_INFO` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_FB_OBJ_ID` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_FB_OBJ_TYPE` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_FB_IMG_LAST_CHECKED` timestamp NULL DEFAULT NULL,
  `POST_FB_IMG_LAST_CHECK_RESULT` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `POST_TWITTER_OBJ_ID` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`POST_ID`),
  UNIQUE KEY `POST_URL_ID` (`POST_URL_ID`),
  KEY `FK_POSTS_POST_CATEGORIES` (`POST_CATEGORY`),
  CONSTRAINT `FK_POSTS_POST_CATEGORIES` FOREIGN KEY (`POST_CATEGORY`) REFERENCES `POST_CATEGORIES` (`CATEG_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=62529 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

POST_CATEGORIES:

CREATE TABLE `POST_CATEGORIES` (
  `CATEG_ID` int(11) NOT NULL AUTO_INCREMENT,
  `CATEG_URL_NAME` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `CATEG_NAME` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`CATEG_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

USERS:

CREATE TABLE `USERS` (
  `USER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_MAIL_ADDRESS` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_USERNAME` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_PASSWORD` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_CREATION_DATE` timestamp NULL DEFAULT current_timestamp(),
  `USER_LAST_LOGIN_DATE` timestamp NULL DEFAULT NULL,
  `USER_FROM_FB` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_FB_MAIL_ADDRESS` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_FB_FULL_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_SPECIAL` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_ACCT_STATUS` varchar(3) COLLATE utf8_unicode_ci DEFAULT 'ENA',
  `USER_POSTS_COUNT` int(5) DEFAULT 0,
  `USER_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_QUOTE` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_ABOUT_MYSELF` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_WEBSITE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_FB` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_TWITTER` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_LOCATION` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_BIRTHDAY` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_PROFILE_PIC` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `USER_PROFILE_PIC_FROM_FB` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`USER_ID`),
  UNIQUE KEY `USER_USERNAME` (`USER_USERNAME`)
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

the EXPLAIN:

mysql> EXPLAIN
    -> SELECT * FROM POSTS
    -> JOIN POST_CATEGORIES on POST_CATEGORIES.CATEG_ID = POSTS.POST_CATEGORY
    -> LEFT JOIN USERS on POSTS.POST_USERNAME = USERS.USER_USERNAME
    -> WHERE POST_STATUS = 'ENA'
    -> AND POST_IS_PUBLIC = 'Y'
    -> ORDER BY POST_IS_STICKY DESC, (TIMESTAMPDIFF(SECOND, POST_CREATION_DATE, now())-(POST_LIKES-POST_DISLIKES)*3600) ASC
    -> LIMIT 0, 12;
+------+-------------+-----------------+--------+--------------------------+---------+---------+--------------------------------+-------+-------------------------------------------------+
| id   | select_type | table           | type   | possible_keys            | key     | key_len | ref                            | rows  | Extra                                           |
+------+-------------+-----------------+--------+--------------------------+---------+---------+--------------------------------+-------+-------------------------------------------------+
|    1 | SIMPLE      | POSTS           | ALL    | FK_POSTS_POST_CATEGORIES | NULL    | NULL    | NULL                           | 61407 | Using where; Using temporary; Using filesort    |
|    1 | SIMPLE      | POST_CATEGORIES | eq_ref | PRIMARY                  | PRIMARY | 4       | MY_DB.POSTS.POST_CATEGORY |     1 |                                                 |
|    1 | SIMPLE      | USERS           | ALL    | NULL                     | NULL    | NULL    | NULL                           |   178 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-----------------+--------+--------------------------+---------+---------+--------------------------------+-------+-------------------------------------------------+
3 rows in set (0.03 sec)

mysql>

and removing the 2 joins:

mysql> EXPLAIN
    -> SELECT * FROM POSTS
    -> WHERE POST_STATUS = 'ENA'
    -> AND POST_IS_PUBLIC = 'Y'
    -> ORDER BY POST_IS_STICKY DESC, (TIMESTAMPDIFF(SECOND, POST_CREATION_DATE, now())-(POST_LIKES-POST_DISLIKES)*3600) ASC
    -> LIMIT 0, 12;
+------+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
|    1 | SIMPLE      | POSTS | ALL  | NULL          | NULL | NULL    | NULL | 61407 | Using where; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.02 sec)

mysql>

TIA

Solution

"Explode-implode" syndrome. Rewrite the query to get the 12 rows first, then JOIN to the other tables.

SELECT ...
    FROM ( SELECT user_id,
                  is_sticky,
                  (TIMESTAMPDIFF(SECOND, POST_CREATION_DATE, now()) -
                     (POST_LIKES-POST_DISLIKES)*3600)  AS  metric
             FROM POSTS
             WHERE  POST_STATUS = 'ENA'
               AND  POST_IS_PUBLIC = 'Y'
             ORDER BY
                 POST_IS_STICKY DESC, metric ASC
             LIMIT  0, 12
         ) AS x
     JOIN POSTS AS p USING(user_id)
     JOIN  POST_CATEGORIES AS pc  ON pc.CATEG_ID = p.POST_CATEGORY
     LEFT JOIN  USERS AS u  ON p.POST_USERNAME = u.USER_USERNAME
     ORDER BY x.POST_IS_STICKY DESC, x.metric ASC

Replace the elipsis with the columns you really need.

Yes, POSTS is looked into a second time, but only for 12 rows, and by the PK. Similarly, the other rows are reached into only 12 times.

Yes, the ORDER BY needs to be repeated. The LIMIT may need repeating if the JOIN creates extra rows (eg, multiple categories).

Consider using

SELECT ...
       ( SELECT GROUP_CONCAT(CATEG_NAME) FROM POST_CATEGORIES
              WHERE categ_id = p.post_categories ) AS Categories,
       ...

instead of JOINing to POST_CATEGORIES, especially if there are multiple categories.

This may help with performance, but only if it is selective enough:

INDEX(post_tatus, post_is_public)