Mysql Join Query optimization

Question

I have two tables in mysql:

Results Table : 1046928 rows.
Nodes Table :  50 rows.

I am joining these two tables with the following query and the execution of the query is very very slow.

select res.TIndex, res.PNumber, res.Sender, res.Receiver, 
sta.Nickname, rta.Nickname from ((Results res join 
Nodes sta) join Nodes rta) where ((res.sender_h=sta.name) and
(res.receiver_h=rta.name));

Please help me optimize this query. Right now if I want to pull just top 5 rows, It takes about 5-6 MINUTES. Thank you.

CREATE TABLE `nodes1` (
  `NodeID` int(11) NOT NULL,
  `Name` varchar(254) NOT NULL,
  `Nickname` varchar(254) NOT NULL,
  PRIMARY KEY (`NodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Results1` (
  `TIndex` int(11) NOT NULL,
  `PNumber` int(11) NOT NULL,
  `Sender` varchar(254) NOT NULL,
  `Receiver` varchar(254) NOT NULL,
  `PTime` datetime NOT NULL,
  PRIMARY KEY (`TIndex`,`PNumber`),
  KEY `PERIOD_TIME_IDX` (`PTime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

do you just mean the misspelling or is there something bigger im missing? i dont see anything logically wrong, just the tra vs rta — jon_darkstar
– jon_darkstar, Commented May 6, 2011 at 0:54
My tables are really big. So, I just cutdown some columns and posted the smaller version. if you think real tables makes sence, I can post them as well. Thank you. — M99
– M99, Commented May 6, 2011 at 0:58
@M99: Please verify that the issue still exists with your stripped-down testcase. It's good that you made one, but you should ensure that you made it properly. :) — Lightness Races in Orbit
– Lightness Races in Orbit, Commented May 6, 2011 at 1:01

John Petrak · Accepted Answer · 2011-05-06 00:57:45Z

4

SELECT  res.TIndex ,
        res.PNumber ,
        res.Sender ,
        res.Receiver ,
        sta.Nickname ,
        rta.Nickname
FROM    Results AS res
        INNER JOIN Nodes AS sta ON res.sender_h = sta.name
        INNER JOIN Nodes AS rta ON res.receiver_h = rta.NAME

Create an index on Results (sender_h)
Create an index on Results (receiver_h)
Create an index on Nodes (name)

answered May 6, 2011 at 0:57

John Petrak

2,92822 silver badges32 bronze badges

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

2 Comments

jon_darkstar Over a year ago

+1 changing where clauses to join conditions and adding those indexes should make a big difference. im guessing that with the full joins and where clauses a huge cartesian product gets created intermediately before its trimmed. can anyone verify this or does it get optimized?

John Petrak Over a year ago

As far as i can tell, there isn't any real performance gain between WHERE over JOINS. I just prefer the JOIN syntax as it makes the meaning very clear. eg these are my table joins and these are my filters. All of the performance gains is by adding the indexes. You can retry your old query and it should run just as fast.

jon_darkstar · Accepted Answer · 2011-05-06 01:18:31Z

1

Joining on the node's name rather than NodeId (the primary key) doesn't look good at all.

Perhaps you should be storing NodeId for foreign key sender and receiver in the Results table instead of name Adding foreign key constraints is a good idea too. Among other things, this might cause indexing automatically depending on your configuration

If this change is difficult, at the very least you should enforce uniqueness on node's name field

If you change the tables definition in this manner, change your query to John's recommendation, and add indexes it should run a lot better and be a lot more readable/better form.

edited May 6, 2011 at 1:18

answered May 6, 2011 at 1:05

jon_darkstar

16.9k7 gold badges35 silver badges38 bronze badges

Collectives™ on Stack Overflow

Mysql Join Query optimization

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related