2 Days ago mysql query was fine, today it's super slow

Question

Okay, so as the title says, the queries were running fine 2 days ago, then all of a sudden yesterday the site was loading very slow. I tracked it down to a couple queries. One I was able to add an index which seems to have helped, but this one I just can't figure out. I tried running a repair and optimize on the tables, and that didn't help. I don't know what could have changed so much that would make it go from less than a second to query to 20+ seconds. Any help would be much appreciated.

SELECT city 
FROM listings LEFT JOIN agencies 
ON listings.agencyid_fk = agencies.agencyid 
WHERE listingstatus IN (1,3) AND appid_fk = 101 AND active = 1 
      AND auction IS NULL AND agencies.multilist = 1 
      AND notagency IS NULL 
GROUP BY city 
ORDER BY city;

I wasn't sure how to export the explain query result to make it readable on here, so I just put it in a code snippet. click run to see it in an html table.

id select_type table    type   possible_keys          key       key_len ref                       rows  Extra
 1 SIMPLE      listings ref    appid_fk,listingstatus appid_fk        2 const                     21699 Using where; Using temporary; Using filesort
 1 SIMPLE      agencies eq_ref PRIMARY,active         PRIMARY         2 mls2.listings.agencyid_fk     1 Using where

And here are the tables...

listings table:

CREATE TABLE mls2.listings (
   listingid INT(11) AUTO_INCREMENT NOT NULL,
   appid_fk SMALLINT(3) NOT NULL DEFAULT '0',
   agencyid_fk SMALLINT(3) NOT NULL DEFAULT '0',
   listingstatus SMALLINT(3),
   city VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
   multilist TINYINT(1),
   auction TINYINT(1),
   PRIMARY KEY (listingid)
) ENGINE = myisam ROW_FORMAT = DEFAULT CHARACTER SET latin1;

agencies table:

CREATE TABLE mls2.agenciesx (
   agencyid SMALLINT(6) AUTO_INCREMENT NOT NULL,
   multilist TINYINT(4) DEFAULT '0',
   notagency TINYINT(1),
   active TINYINT(1),
  PRIMARY KEY (agencyid)
) ENGINE = myisam ROW_FORMAT = DEFAULT CHARACTER SET latin1;

Your query concerns 9 columns. You really don't need to tell us about any of the others - although in this instance it does reveal a distinct lack of normalization! — Strawberry, Apr 26 '18 at 09:33
Has the load on the MySQL server increased on the "slow" day as compared to the "fast" day? Has the size of the table being queried increased? — pri, Apr 26 '18 at 09:38
@Strawberry INNER JOIN does seem to speed it up a tiny bit, but it's sitll very slow. As for as the normalization, i inherited this table structure and I'm not the best when it comes to mysql. Everything I know is self taught. — John, Apr 26 '18 at 09:59
Your EXPLAIN suggests that listingstatus is indexed, but your table says that it isn't. Can you make sure that the code you provide is consistent. — Strawberry, Apr 26 '18 at 10:07
...and mixing collations is never going to end well. Just use utf8 (or utf8mb4) all the way through. — Strawberry, Apr 26 '18 at 10:09
And now to a more serious point: NEVER use a GROUP BY clause in the absence of any aggregating functions. It may perform faster than DISTINCT, but it's likely to lead to all sorts of errors as queries grow in complexity. Worse, these errors may not always be immediately apparent. — Strawberry, Apr 26 '18 at 10:14
@Strawberry would i do something like this to convert it to utf8mb4? https://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5 — John, Apr 26 '18 at 11:25
Yes, but look at the comments too. Also, Rick James has written extensively on this subject (perhaps a little too extensively!), but his posts are worth a read too - once you grabbed a suitably sized coffee. https://stackoverflow.com/questions/38363566/trouble-with-utf-8-characters-what-i-see-is-not-what-i-stored — Strawberry, Apr 26 '18 at 11:39

score 0 · Answer 1 · answered Apr 26 '18 at 10:25

Once you've taken on board the comments above, try adding the following indexes to your tables...

INDEX(city,listingstatus,appid_fk,auction)
INDEX(active,multilist,notagency)

In both cases, the order in which columns are arranged in the index may make a difference, so play around with those, although there are so few rows in the agencies table, that that one won't really matter.

Next, get rid of the GROUP BY clause, and write your query as follows.

SELECT DISTINCT l.city 
           FROM listings l
           JOIN agencies a
             ON a.agencyid = l.agencyid_fk 
          WHERE l.listingstatus IN (1,3) 
            AND l.appid_fk = 101 
            AND a.active = 1 
            AND l.auction IS NULL 
            AND a.multilist = 1 
            AND a.notagency IS NULL 
          ORDER 
             BY city;

Note: Although irrelevant for this particular problem, your original question showed that this schema is desperately in need of normalisation.

2 Days ago mysql query was fine, today it's super slow

1 Answers1