Re: No Query result with more then 4000 records

[Peter Brawley]

These fora aren't designed for images and IAC aren't convenient eg for cutting & pasting. To maintain readable text formatting, surround text with BBCode code tags.

Explain reveals two huge problems: volume, and the query design prevents index use.

On volume, the optimiser expects to process 93 billion allfin_pass166 rows, yikes, and 9 million T10RTAAK rows.

On index use, MySQL indexes can't see inside functions so most evrything referenced has to come from reading the table.

The Unique key on allfin_pass166 is utterly useless, that column is the PK. Removing it will slightly improve Insert performance, but compared to the above factors, that's small potatoes.

Consider moving the complicated join logic to (i) preparation of intermediate tables that can be joined using indexes or of this query runs often perhaps (ii) permanent denormalised intermediate tables.



Edited 1 time(s). Last edit at 04/06/2018 09:04AM by Peter Brawley.