# JOIN optimization

**URL:** <https://forums.percona.com/t/join-optimization/113>\
**Category:** Other MySQL® Questions\
**Created:** [November 1, 2006, 11:12pm UTC](https://forums.percona.com/t/join-optimization/113 "2006-11-01T23:12:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bengoerz](https://avatars.discourse-cdn.com/v4/letter/b/ecb155/32.png) [@bengoerz](https://forums.percona.com/u/bengoerz)\
**Post date:** [November 1, 2006, 11:12pm UTC](https://forums.percona.com/t/join-optimization/113/1 "2006-11-01T23:12:59Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![toasty](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@toasty](https://forums.percona.com/u/toasty)\
**Post date:** [November 2, 2006, 3:59am UTC](https://forums.percona.com/t/join-optimization/113/2 "2006-11-02T03:59:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [November 2, 2006, 6:33am UTC](https://forums.percona.com/t/join-optimization/113/3 "2006-11-02T06:33:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![migandhi](https://avatars.discourse-cdn.com/v4/letter/m/a9a28c/32.png) [@migandhi](https://forums.percona.com/u/migandhi)\
**Post date:** [December 7, 2006, 4:27am UTC](https://forums.percona.com/t/join-optimization/113/4 "2006-12-07T04:27:58Z")

</div>

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.