CODEWITHSUNDEEP
mysqlsqlperformance
Louisa
Louisa

Reputation: 550

MySQL performance problems for a simple select query

Have a MySQL performance problem that I don't get.

This query most often takes ~1 second to execute, but sometimes it takes 5+ minutes.

SELECT  sb.id AS id, sb.name AS name,
ROUND(sb.dkprice / 100, 0) AS price, bs.name AS shopname, country
    FROM  shopbikes sb, categoriesshopbikes csb, shops s
    WHERE  sb.id = csb.`shopbikeid`
      AND  csb.categoryid = 93
      AND  sb.brand = 'Ghost'
      AND  dkprice > 0
      AND  sb.dkprice IS NOT NULL
      AND  sb.`shopid` = s.id
    ORDER BY  dkprice
    LIMIT  1

There are sometimes 5-8 of these queries running at the same time, for different brands.

When I execute SHOW FULL PROCESSLIST, I can see a number of these queries are running and some of them have been running for a long time (up to several minutes).

The tables in the query all have indexes for all the relevant columns (i.e. the ones that the tables are matched by).

Size of the tables:

Any idea what is going wrong and how I can fix it?

Update: I have converted the query to an explicit join:

SELECT  sb.id AS id, sb.name AS NAME,
ROUND(sb.dkprice / 100, 0) AS price, s.name AS shopname, country
    FROM  shopbikes sb
    JOIN  categoriesshopbikes csb  ON (sb.id = csb.shopbikeid)
    JOIN  shops s  ON (sb.shopid = s.id)
    WHERE  csb.`categoryid` = 93
      AND  sb.`brand` = 'Cannondale'
      AND  dkprice > 0
      AND  dkprice IS NOT NULL
    ORDER BY  dkprice
    LIMIT  1

It didn't help. The query still often takes a long time to execute.

Explain output: https://i.sstatic.net/xilvV.png

Upvotes: 1

Views: 75

Answers (2)

Rick James
Rick James

Reputation: 142208

csb needs INDEX(shopbikeid, category_id) in either order. If categoriesshopbikes is a many-to-many mapping table, did you follow all the recommendations here?

sb needs INDEX(brand, dkprice), in that order.

I think AND dkprice IS NOT NULL is redundant with AND dkprice > 0.

Upvotes: 1

pablochan
pablochan

Reputation: 5715

FROM shopbikes sb, categoriesshopbikes csb, shops s

I'd have to look at the query plan, but I think this line is the problem. This kind of FROM clause will probably create a cartesian product from the listed tables (i. e. 600k * 100k * 100 rows).

I'm guessing that proper JOIN clauses will help

FROM shopbikes sb JOIN categoriesshopbikes csb ON (sb.id = csb.shopbikeid) JOIN shops s ON (sb.shopid = s.id)

Upvotes: 0

Related Questions