Re: A slow query - Help please?

hubert depesz lubaczewski wrote:
> ditch the inheritance. it is no good, and makes everything too
> complicated to work with.

Would love to, but that's what the system we use generates (MMBase, for
the record). We can probably rework the generated tables, but it isn't
entirely certain that won't break MMBase.

> in case you can't, do something similar to this:
>
> select * from
> (
> select * from only table_a order by number desc limit 25
> union
> select * from only table_b order by number desc limit 25
> union
> select * from only table_c order by number desc limit 25
> ) x
> order by number desc limit 25;

Actually, the query isn't valid this way.

The "order by" clauses aren't allowed there - I don't know about the
"limit" ones, but they're pointless w/o "order by" anyway.

And of course 'select *' isn't going to work in the subqueries, the base
table contains less columns than the inherited tables.

After removing the "order by" and "limit" clauses, it is about exactly
as slow as querying the inherited table directly:

select * from table_a order by number desc limit 25;

I'm afraid this didn't quite solve the problem...
We probably need to get rid of the inheritence - it can be done, as the
same product manages to run on MySQL as well...

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //