MySQL Query Optimization needed

Question

I'm trying to do a query in mysql

   SELECT *
FROM table_name1
WHERE codregister IN
    (SELECT register
     FROM tablename2
     WHERE city LIKE '%paris%')
ORDER BY date DESC

In table_name "codregister" is a primary key but in tablename2 "register" field column is a index (primary key on tablename2 is an autoincrement).

In the table_name1 doesn´t have matches with the tablename2 but the execution time of the query is to slow. Someone can recommend to improve the query?

It's slow because you have nested selections, so if you want to speed it up that would be the first place to look (using INNER JOIN or something to the effect). — Rogue
– Rogue, Commented Nov 18, 2013 at 12:08
time execution dude? I don't know who you're talking about... — NDM
– NDM, Commented Nov 18, 2013 at 12:22

Augusto · Accepted Answer · 2013-11-18 12:26:14Z

1

Check this considerable difference from csf answer

SELECT * 
FROM 
    table_a a 

    INNER JOIN 

    (SELECT register 
     FROM table_b 
     WHERE city like '%paris%') b
ON(a.codregister=b.register)
ORDER BY a.date

You're only projecting the only field of B that you need, and you are filtering B records before the join.

answered Nov 18, 2013 at 12:26

Augusto

8176 silver badges18 bronze badges

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

2 Comments

Hanzo Over a year ago

Thanks, your reply was useful to me, the execution time is more faster with your query.

Hanzo Over a year ago

One more question related, Is advisable always filter with a subquery the table that is joined even if the subquery not have like or where clause ?

csf · Accepted Answer · 2013-11-18 12:16:29Z

1

Use JOIN instead , and make sure joining variables are indexed .

SELECT * FROM table_name1 a
JOIN tablename2 b on a.codregister = b.register
where b.city like '%paris%'
ORDER BY a.date  DESC

Also for any query you write try to use 'EXPLAIN' to find more about your query Ref : http://dev.mysql.com/doc/refman/5.0/en/explain.html

answered Nov 18, 2013 at 12:16

csf

5871 gold badge5 silver badges17 bronze badges

1 Comment

Hanzo Over a year ago

Thanks also for your reply. ;)

csf · Accepted Answer · 2013-11-21 11:57:36Z

0

i agree with 0R10N Answer , filtering before join can make join faster . ThankI

edited Nov 21, 2013 at 11:57

answered Nov 18, 2013 at 12:39

csf

5871 gold badge5 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL Query Optimization needed

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related