JOIN optimization

MySQL & MariaDB Other MySQL® Questions
1

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?

2

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

3

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.

4

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.