MySQL Forums
Forum List  »  Optimizer & Parser

Re: slow query sorting on group maximum
Posted by: Dong Hoon Van Uytsel
Date: November 07, 2010 07:24AM

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).

Options: ReplyQuote


Subject
Views
Written By
Posted
1359
November 06, 2010 10:56AM
Re: slow query sorting on group maximum
1299
November 07, 2010 07:24AM
1369
November 07, 2010 01:05PM
1905
November 19, 2010 07:05PM
1293
November 19, 2010 07:20PM
1293
November 19, 2010 07:14PM


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.