CODEWITHSUNDEEP
mysql
Brian
Brian

Reputation: 27412

increase speed for MySQL JOIN for two large tables

I have to JOIN to large tables in a MySQL query and it takes really long - approximately 180 seconds. Are there any tips for optimizing a merge?

My table has 10 fields. I am only using 4 in the query - all strings. Table has about 600,000 rows and the result should have about 50 rows.

The four used rows are: Title, Variables, Location, Date

Here is my query:

SELECT DISTINCT t1.Title, t1.Variables FROM `MyTABLE` t1 JOIN `MyTABLE` t2  
USING (Title,  Variables) 
WHERE (t1.Location, t1.Date) = ('Location1', 'Date1') 
AND (t2.Location, t2.Date) = ('Location2', 'Date2')

Upvotes: 8

Views: 29457

Answers (8)

Mohit Satish Pawar
Mohit Satish Pawar

Reputation: 61

I did two separate joins and combined result using the union operator. I was getting good improvements in time. SELECT t1.Title, t1.Variables FROMMyTABLE t1 JOINMyTABLEt2 on (t1.Location, t1.Date) = ('Location1', 'Date1') UNION SELECT t1.Title, t1.Variables FROMMyTABLE t1 JOINMyTABLEt2 on (t2.Location, t2.Date) = ('Location2', 'Date2');

Make sure that both queries have the same number of column and same data type for each column. Also, check the order of select clause.

Upvotes: 0

Roland Bouman
Roland Bouman

Reputation: 31991

Like others pointed out, you need proper indexes. For this particular query, you can benefit from indexes like:

(Location, Date) or (Date, Location) (for the WHERE clause) and (Title, Variables) or (Variables, Title) (for the join condition, ON clause)

It would be helpful to know exactly the size (that is, datatype) of the location, Date, Title, and Variables columns, as a large index is likely to be slower than a small one.

Finally, just a tip: I would not use fancy comparison constructs like you do. The 

USING (Title,  Variables)

is probably ok, but I would certainly check if 

(t1.Location, t1.Date) = ('Location1', 'Date1')

and

(t2.Location, t2.Forecast_date) = ('Location2', 'Date2')

are behaving like you expect. SO I would definitely run EXPLAIN on it, and compare the output with a "regular" old fashioned comparison, like so:

    t1.Location      = 'Location1'
AND t1.Date          = 'Date1'
AND t2.Location      = 'Location2'
AND t2.Forecast_date = 'Date2'

You may argue that logically, it is the same and it shouldn't matter - you'd be right. But then again, MySQL's optimizer isn't very smart, and there is always a possibility of bugs, especially with features that aren't used a lot. I think this is such a feature. So i would at least try to EXPLAIN and see if these alternate notations are evaluated the same.

But what BenoKrapo pointed out, would it not be easier to do something like this:

SELECT Title, Variables 
FROM   MyTABLE
WHERE  Location = 'Location1' AND Date = 'Date1' 
OR     Location = 'Location2' AND Date = 'Date2'
GROUP BY Title, Variables
HAVING COUNT(*) >= 2

EDIT: I changed HAVING COUNT(*) = 2 to HAVING COUNT(*) >= 2. See comments (thanks again, BenoKrapo)

EDIT: days after posting this answer, I found this post from Mark Callaghan, MySQL Architect for Facebook: http://www.facebook.com/note.php?note_id=243134480932 Essentially, he describes how similar-but-different 'smart' comparisons deliver abysmal performance due to MySQL optimizer bug. So my point is, try to unfancy your syntax when you suffer, you might have hit a bug.

Upvotes: 9

Brian
Brian

Reputation: 27412

This might be cheating a little bit, but I actually found it easier to JOIN the two queries together in PHP after the query. This only works because I am selecting two distinct variables.

$query = "SELECT DISTINCT Title, Variables FROM 
MyTABLE WHERE Location='Location1' AND Variable='Variable1'";

$result = mysql_result($query);
while ($row = mysql_array_assoc($result)) {
    $Title = $row['Title'];
    $Variables = $row['Variables'];
    $Array_result1[$Title] = $Variables;
}


$query = "SELECT DISTINCT Title, Variables FROM 
MyTABLE WHERE Location='Location2' AND Variable='Variable2'";

$result = mysql_result($query);
while ($row = mysql_array_assoc($result)) {
    $Title = $row['Title'];
    $Variables = $row['Variables'];
    $Array_result2[$Title] = $Variables;
}

$Array_result = array_intersect($Array_result1, $Array_result2);

I Liked the idea of only using one MySQL query to merge the two queries, but this is so much faster.

Upvotes: 1

Benoît Vidis
Benoît Vidis

Reputation: 3902

Make sure the fields you are matching on are indexed. Matching numeric values is also a quicker than strings.

But wouldn't it be simpler to just write

SELECT DISTINCT 
  Title, 
  Variables 
FROM `MyTABLE`
WHERE 
  Location = 'Location1' AND Date = 'Date1' 
  OR
  Location = 'Location2' AND Date = 'Date2'

Upvotes: 1

mjsabby
mjsabby

Reputation: 1139

Can you prepend your SQL statement with "EXPLAIN" and then re-run it, it's likely because of missing indices on the columns you're joining on.

Also trying using STRAIGHT_JOIN and mention the table that is slower in size in the left, and the bigger one on the right to hint MySQL to choose the first table.

Upvotes: 1

Nitin Midha
Nitin Midha

Reputation: 2268

Try to use composite index on columns in where clause and try to put all other columns in select in Included Columns, this will save traditional look up cost.

Upvotes: 0

Tor Valamo
Tor Valamo

Reputation: 33809

Without the description of the tables and the query there's little we can do to help.

There are several things that can determine the speed of a join.

  • The database engine: Are you using InnoDB or MyISAM? Or maybe any other engine? Some are faster at lookups than others, which affects joins.
  • Indexes: Are the appropriate match-columns indexed?
  • Partition indexes: Maybe you can partition the table by indexes to make it even faster?

Also, look at EXPLAIN query which will look at all the steps that mysql takes to execute it. It could help you tremendously.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300769

Yes. Create appropriate indexes based upon the queries being run against the tables involved.

Upvotes: 2

Related Questions