Re: slow query sorting on group maximum

[Dong Hoon Van Uytsel]

Hello Rick,

Rick James Wrote:
-------------------------------------------------------
> [...]
>
> DISTINCT and GROUP BY -- usually redundant.

yes, i removed "distinct", but no apparent efficiency gain

> LIMIT and OFFSET; smells like pagination?

indeed. is pagination considered bad...? which alternative(s) would you suggest?

> 'OR' virtually eliminates any chance of using an
> INDEX; can you get rid of NULLs in `expiry`?

i would be able to set expiry not null and default to some constant distant time in the future, e.g. 2200-01-01 00:00. did this, altered the table definition, but no apparent efficiency gain.


> Other fields, such as PATIENTID can be NULL; is
> that reasonable?

i could easily avoid it by associating interventions with unknown patient to an "anonymous" patient. i have adapted this, altered the table definition, but no apparent efficiency gain.

> For intervention, why have an AUTO_INCREMENT, when
> you seem to have a perfectly usable UNIQUE key of
> INTID?

disk space: i read and was told that the primary key is copied with every other index entry, and i wanted to use UUIDs as foreign keys to make db merges etc easier.

> Is this query a subquery in something else?

no, but a lag of 1.5 seconds for every page feels (too) slow.

> Are any pairs of tables 1:1?

do you mean is there a one-to-one relation between two tables? yes, there are in the DB, but not in the involved tables intervention, data, identity.

> Is innodb_buffer_pool_size set to 70% of
> _available_ RAM?

actually 60% (1.2G of 2G).