MySQL Performance: MySQL => JOIN optimization

Members

Help

Home

Home » Performance » MySQL » JOIN optimization

Show: Today's Messages :: Show Polls :: Message Navigator

JOIN optimization [message #318]

Wed, 01 November 2006 23:42

bengoerz
Messages: 6
Registered: November 2006

Junior Member

I have a couple tables - approx 320,000 and 64,000 rows - which I must query with INNER JOIN. Currently, these operations take between 8-60 minutes to complete depending on the number of columns joined and returned.

Can you recommend techniques to optimize such large JOIN operations? Is there perhaps a server other than MySQL which is more efficient at this?

Report message to a moderator

Re: JOIN optimization [message #321 is a reply to message #318 ]

Thu, 02 November 2006 04:29

toasty
Messages: 19
Registered: August 2006
Location: UK

Junior Member

Hi,

Those tables are pretty small, and joining them should be very very fast, depending on the detail of your query.

At a guess I suspect that your problem is that you don't have indexes on the columns that you're joining on, but if you could post explain plans and show create table statements it'd be very useful.

Toasty

Report message to a moderator

Re: JOIN optimization [message #322 is a reply to message #321 ]

Thu, 02 November 2006 07:03

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Yeah,

Please provide explain.
If you join tables without index even two 10.000 row tables would mean 100.000.000 row combinations to examine.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: JOIN optimization [message #323 is a reply to message #322 ]

Thu, 02 November 2006 09:08

bengoerz
Messages: 6
Registered: November 2006

Junior Member


mysql> EXPLAIN
    -> SELECT *
    -> FROM esbsaugdump
    ->  INNER JOIN
    ->  allbanks
    ->    ON esbsaugdump.swiftcode = allbanks.swift
    -> WHERE swiftcode IS NOT NULL;
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | esbsaugdump | ALL  | NULL          | NULL |    NULL | NULL |  64217 | Using where |
|  1 | SIMPLE      | allbanks    | ALL  | NULL          | NULL |    NULL | NULL | 319110 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+
2 rows in set (0.00 sec)

[Updated on: Thu, 02 November 2006 09:15]

Report message to a moderator

Re: JOIN optimization [message #324 is a reply to message #323 ]

Thu, 02 November 2006 09:28

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

You're missing indexes:

ON esbsaugdump.swiftcode = allbanks.swift

You should have one or another of this columns indexed or better both so MySQL will have better choices.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: JOIN optimization [message #325 is a reply to message #324 ]

Thu, 02 November 2006 10:37

bengoerz
Messages: 6
Registered: November 2006

Junior Member

After indexing both these columns, the query runs in .6 seconds!

Thanks, and sorry for the newbie question. Laughing

Report message to a moderator

Re: JOIN optimization [message #456 is a reply to message #318 ]

Thu, 07 December 2006 04:57

migandhi
Messages: 9
Registered: December 2006

Junior Member

Please check in the .ini
(my.ini or the initialization file you r server reads by default)

file whether enough buffer has been allocated for reading
queries.

if not try to increase the size and just re-start you MySQL server.
i cannot assure results .
But i hope it proves useful.

Report message to a moderator

Previous Topic:	JOIN issue
Next Topic:	Efficiency: Num vs Char

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 6 21:41:07 EDT 2009

Total time taken to generate the page: 0.01315 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.