Mysql – Need help fixing query/indexes

Sorry, I'm a beginner, and I inherited a massive database with huge querying issues.

It's for a forum, and there's a piece of shoddy software (image gallery extension) that is a trainwreck that I'm trying to fix whilst I take some time on the side to finish up my own custom-coded cleaner version that should fix all the issues.

I contacted the extension's coder and he basically stopped responding.

So here's the query:

SELECT    p.id_picture, 
          p.commenttotal, 
          p.totalratings, 
          p.rating, 
          p.filesize, 
          p.views, 
          p.thumbfilename, 
          p.title, 
          p.id_member, 
          m.real_name, 
          p.date, 
          p.description, 
          p.mature, 
          c.view, 
          (p.rating / p.totalratings ) AS ratingaverage, 
          v.id_picture                 AS unread, 
          mg.online_color 
FROM      smf_gallery_pic AS p 
LEFT JOIN smf_members     AS m 
ON        ( 
                    m.id_member = p.id_member) 
LEFT JOIN smf_membergroups AS mg 
ON        ( 
                    mg.id_group = IF(m.id_group = 0, m.id_post_group, m.id_group)) 
LEFT JOIN smf_gallery_usersettings AS s 
ON        ( 
                    s.id_member = m.id_member) 
LEFT JOIN smf_gallery_catperm AS c 
ON        ( 
                    c.id_group IN (0,8) 
          AND       c.id_cat = p.id_cat) 
LEFT JOIN smf_gallery_log_mark_view AS v 
ON        ( 
                    p.id_picture = v.id_picture 
          AND       v.id_member = <removed> 
          AND       v.user_id_cat = p.user_id_cat) 
WHERE     (( 
                              s.private = 0 
                                        || s.private IS NULL) 
          AND       ( 
                              s.password = '' 
                                        || s.password IS NULL) 
          AND       p.user_id_cat != 0 
          AND       p.approved = 1) 
                    || (p.approved = 1 
AND       p.user_id_cat = 0 
AND       ( 
                    c.view IS NULL 
                              || c.view = 1)) 
GROUP BY  p.id_picture 
ORDER BY  p.id_picture DESC 
LIMIT     60;

Here's the explain:

| id | select_type | table | partitions | type   | possible_keys                                                               | key        | key_len | ref                         | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+--------+-----------------------------------------------------------------------------+------------+---------+-----------------------------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | p     | NULL       | ALL    | PRIMARY,id_cat,user_id_cat,id_member,rating,views,commenttotal,totalratings | NULL       | NULL    | NULL                        | 41558 |     9.01 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY                                                                     | PRIMARY    | 3       | hypnoforum_new.p.id_member  |     1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | mg    | NULL       | eq_ref | PRIMARY                                                                     | PRIMARY    | 2       | func                        |     1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | s     | NULL       | eq_ref | PRIMARY                                                                     | PRIMARY    | 3       | hypnoforum_new.m.id_member  |     1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | c     | NULL       | ALL    | id_cat                                                                      | NULL       | NULL    | NULL                        |     1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | v     | NULL       | ref    | user_id_cat,id_member,id_picture                                            | id_picture | 4       | hypnoforum_new.p.id_picture |   405 |   100.00 | Using where                                        |

I'm kind of at the end of my wits. I haven't done much DB work at all besides the theoretical. Thank you in advance.

Oh, and I almost forgot, here are the query times.

# Query_time: 121.589674  Lock_time: 0.000551  Rows_sent: 60  Rows_examined: 27233114  Rows_affected: 0

They can range up to 10 minutes…

The server hardware is a Xeon 1270V6 with 32GB of RAM running on 450GB NVMe storage, so I don't think it should be this slow.

Okay, so I have a temporary fix. Apparently, smf_gallery_log_mark_view is the culprit that is causing enormous query times. I believe it is using the entire table in the join for some reason. I deleted all the information in that table, but I would like to eventually achieve a long-term solution. Thanks for anyone reading all of this.

EDIT:

Sorry this is for MySQL and I’m currently running Percona 5.7.

Here's a db-fiddle with table CREATE statements and the SELECT query.