Optimizing a MySQL Join Query?

Question

I have the following query:

select * 
 from products as p
 left join files as f on f.contentid = p.productid
   where 
     p.state = 'active' AND 
     p.price > 0 order by p.created DESC 
   LIMIT 2200

Currently, this query takes about 1.7 seconds for 2200 rows. What I don't understand is, why does this time not decrease when I change the limit from 2200 to 10, and more importantly, what I can do to speed it up?

Time to order the data is the same for 10, 200, 2200 and all the rows. First data will be ordered then limit will be applied. — botzko
– botzko, Commented Mar 12, 2012 at 12:54
SELECT p.field1, p.field2, p.fieldn FROM products p, files f WHERE f.contentid = p.productid AND p.state = 'active' AND p.price > 0 ORDER BY p.created DESC LIMIT 2200 — Gabriel Santos
– Gabriel Santos, Commented Mar 12, 2012 at 12:55
If you don't need every field from the two tables, you may also want to consider selecting the specific fields (instead of *) to reduce the size of the returned rows. That's helped me in situations in the past (especilly text fields). — Kory Sharp
– Kory Sharp, Commented Mar 12, 2012 at 12:55

John Woo · Accepted Answer · 2012-03-12 12:57:55Z

5

By adding INDEX on files [contentid] and products [productid], it can speed up your query.

answered Mar 12, 2012 at 12:57

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dotoree · Accepted Answer · 2012-03-12 12:53:13Z

1

Try :

EXPLAIN select * 
 from products p
 left join files f on f.contentid = p.productid
   where 
     p.state = 'active' AND 
     p.price > 0 order by p.created DESC 
   LIMIT 2200

to see what indexes you need

answered Mar 12, 2012 at 12:53

dotoree

2,9911 gold badge26 silver badges29 bronze badges

4 Comments

rockstardev Over a year ago

+ Options id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p ALL NULL NULL NULL NULL 1577 Using where; Using temporary; Using filesort 1 SIMPLE f ALL NULL NULL NULL NULL 1595

dotoree Over a year ago

As I see possible_keys column is null, so you got no possible keys for the query to use. Try to add indexes for files contentid and for products productid

rockstardev Over a year ago

I did that, it fixed the problem, but I'm unsure as to how to use explain. It ended up not giving me the answer... or at least, I don't know how to make sense of it...

dotoree Over a year ago

@RD Here is a tutorial about explain eecho.info/Echo/database/using-explain

jammer · Accepted Answer · 2012-03-12 12:58:37Z

1

Usually I just play with the query to see what minimizes runtime the best. If necessary, work out the problem using Relational Algebra and look for places to optimize from there. Here's my suggestion:

 SELECT * 
 FROM products as p, files f
    WHERE f.contentid = p.productid 
    AND p.state = 'active'
    AND p.price > 0 
    ORDER BY p.created DESC
    LIMIT 2200

answered Mar 12, 2012 at 12:58

jammer

238 bronze badges

Collectives™ on Stack Overflow

Optimizing a MySQL Join Query?

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related