How to optimize this mysql query?

Question

I have this query

SELECT id FROM btn 
WHERE parentid =0
AND (mem_id =ANY(SELECT mem_id FROM network WHERE frd_id='401') || mem_id ='401')
ORDER BY btn.date DESC LIMIT 0,20

& this query

SELECT mem_id FROM net WHERE frd_id='401'

gives me result like this

mem_id
34
45
633
24
22

I want to optimize the above main query which is currently taking 46 second after scanning 13,373 records of btn table

Please suggest me hw can I optimize this query?

thnks

We can't really help without knowing your database structure. — Bojangles
– Bojangles, Commented Oct 1, 2011 at 13:42
Do you have any indexes at all? If yes, where? What is EXPLAIN showing? — Shef
– Shef, Commented Oct 1, 2011 at 13:48
Field Type Null Key Default Extra id bigint(20) NO PRI (NULL) auto_increment — Sparkx
– Sparkx, Commented Oct 1, 2011 at 14:19

Shackrock · Accepted Answer · 2011-10-01 14:33:46Z

1

You will want to index the values that you search on. So, based on the two above:

parentid, frd_id, mem_id

That should help considerably...

answered Oct 1, 2011 at 14:33

Shackrock

4,72111 gold badges51 silver badges76 bronze badges

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

Comments

Johan · Accepted Answer · 2011-10-08 13:57:11Z

0

SELECT DISTINCT b.id FROM btn b
INNER JOIN network n ON (n.mem_id = b.mem_id)  
WHERE b.parentid = '0'
AND ('401' IN (n.frd_id, n.mem_id))
ORDER BY b.date DESC 
LIMIT 20 OFFSET 0

Make sure to have indexes on

btn.mem_id, btn.parentid, btn.date
network.mem_id, network.frd_id

edited Oct 8, 2011 at 13:57

answered Oct 2, 2011 at 6:35

Johan

77.4k28 gold badges205 silver badges346 bronze badges

Collectives™ on Stack Overflow

How to optimize this mysql query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related