On Fri, 18 Nov 2005, Matthias Teege wrote:
>Christian Smith schrieb:
>
>> What is better is a view that dynamically returns rows based on the
>> underlying pairs table, rather than inserting into cols:
>
>yup, I like views a lot but in my current case I have a speed problem.
>My "pairs" has more then 700.000 records. I need 10 fields so I have 10
>joins in my query. The result of the query/view are 6000 Records. A
>select on the view is fast enough but I need a "select * from myview
>group by id" and this takes more the 10 seconds on a Xeon CPU 3.20GHz.
>
>Is there something I can do to speed up this beside create a new table?
Index the pairs table, like I do in my schema. You might want to index by
id and field as these are primarily what you use to read data in this
case, though it is always going to be slower on the table scan case, but
at least the joins should be pretty optimal:
create table pairs (
id,
field,
value,
primary key(id,field) on conflict replace );
Now, all lookups used to implement the view are done using index lookups.
If you find even that not fast enough for repeated "table" scans, then you
could create a cache table, initialised from the view:
create temporary table pairs_view_cache as select * from pairs_view;
...
<do selects on pairs_view_cache>
...
drop table pairs_view_cache;
Given a desired view of (id, name, foo), the following view will suffice:
create view pairs_view as
select name.id, name.value as name, foo.value as foo
from pairs as name left join pairs as foo
on name.id = foo.id
where name.field = 'name' and foo.field = 'foo';
For extra columns, add extra joins and extra where clauses. This is all
quite easy to generate in code given template fields.
>
>Many thanks
>Matthias
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
