Re: Sorting over multiple JOINS

[Günther Schmidt]

Rick James Wrote:
-------------------------------------------------------
> A cross-table JOIN is essentially unoptimizable
> (except in the non-defunct Akiban engine).

But then normalization contradicts performance. Only 500.000 rows result in a huge performance problem.

Will the sorting be faster if I raise innodb_buffer_pool_size or sort_buffer_size?

The resultset is about 500 MB if I select it to a temporary table.

> INDEX (thingID , info1)
> is backwards. You need info1 to be first in order
> to be more efficient with the WHERE clause.

But I thought that thingID comes first because it is the foreign key.

> Please provide the EXPLAIN SELECT of the query
> that is taking so long. There may be some
> additional clues. (And it would be handy to have
> the actual SHOW CREATE TABLE and SELECT. Again,
> there can be subtle things going on that your
> summary failed to capture.)

That is a minimal example of my real data structure which I can not simply post here.