Re: Sorting over multiple JOINS
Posted by: Günther Schmidt
Date: January 23, 2015 02:17AM

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.

Options: ReplyQuote


Subject
Written By
Posted
January 21, 2015 02:14PM
Re: Sorting over multiple JOINS
January 23, 2015 02:17AM
January 24, 2015 12:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.