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.rating / p.totalratings ) AS ratingaverage, 
          v.id_picture                 AS unread, 
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.


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.

  • 1
    Is smf_gallery_log_mark_view a VIEW? Or a TABLE? Please provide SHOW CREATE.... – Rick James Apr 17 '18 at 18:12
  • 1
    How many rows in smf_gallery_catperm ? – Rick James Apr 17 '18 at 18:14
  • Very few rows are in smf_gallery_catperm, it's holding only about 10-20 rows with just categories of gallery images. – al4nw31 Apr 25 '18 at 3:42

There are many things 'wrong' with the query. I don't know which are important, nor do I know which can safely be 'fixed'.

OR is hard to optimize. There are several cases of OR, such as

s.private = 0 || s.private IS NULL

If practical, consistently pick either NULL or 0 for the case in question. After doing that, we can discuss possible INDEX usage.

The contents of ON makes a difference. For example:

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 ) 

The "right" stuff to put into ON is the things that define how the tables are related (the 1st and 3rd items in this case). The "filtering" (v.id_member = <removed>) belongs in the WHERE clause. In the case of LEFT, it matters.

Don't use LEFT blindly. It has a particular usage, and should not be used when it is not needed. Because of my comment above and my not understanding of the query's goal, I cannot advise on which of these to do:

  • Change LEFT JOIN to JOIN
  • Move the filtering to WHERE
  • or perhaps some other reformulation.

This may be a case of "explode-implode". The JOINs may expand the number of rows involved (note the 405 in EXPLAIN), followed by a collapse (GROUP BY).

GROUP BY may be misused. There is no 'aggregate' (COUNT / SUM / ...), so GROUP BY should probably be replaced by DISTINCT. You may even be getting random values for some columns since perhaps not all columns are related to p.id_picture.

INDEX(id_picture) may be useful. Please provide SHOW CREATE TABLE. If id_picture is already the PRIMARY KEY, this paragraph is irrelevant. Having some kind of index on id_picture might let it avoid Using temporary; Using filesort. 27233114 says that it probably does not have such an index, or the Optimizer chose to ignore it.

smf_gallery_log_mark_view might be a VIEW? If it is, then all sorts of complications could muddy my analysis.

innodb_buffer_pool_size should be about 20G; is it? Are the tables all InnoDB?

... Answer some of my questions, deal with some of my suggestions, then I will take another look at things.

|improve this answer|||||
  • Answers to some of your questions can be found in the linked db-fiddle (which shows the CREATE statements for all the tables involved): smf_gallery_log_mark_view is a table and id_picture is a primary key. – dbdemon Apr 23 '18 at 21:41

You are using too many left joins, which slowed down the query performance. You need proper indexing on columns which is used in where and ON clause. Id's have primary keys which has already clustered indexes. You may check statement execution plan by using EXPLAIN for proper indexing. EXPLAINis used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. MySQL explains how it would process the statement.

|improve this answer|||||

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.