On 11 Jun 2009, at 10:19am, Antti Nietosvaara wrote:
> On Thursday 11 June 2009 11:50:56 Simon Slavin wrote:
>> On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote:
>>> CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
>>> where (C=1 OR C=2) will reflect the permissions of the
If you only have a handful of values for C and are already going to
the trouble of creating separate views for each C, you could partition
your data into separate tables for each value of C and maybe create
another table containing the list of values of C and maybe the number
of items in each C
On Thursday 11 June 2009 16:30:12 Igor Tandetnik wrote:
> > Ah, this would indeed explain the slowdown. I was hoping views would
> > translate into the "where" part of the query, like:
> > SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2);
>
> I predict this last query wouldn't
Antti Nietosvaara wrote:
> On Thursday 11 June 2009 14:54:04 Igor Tandetnik wrote:
>> Because this query is no longer a simple select. It is translated
>> internally into
>>
>> select min(C) from
>> (SELECT * FROM T WHERE (C=1 OR C=2));
>
> Ah, this would indeed explain the slowdown. I was hoping
On Thursday 11 June 2009 14:54:04 Igor Tandetnik wrote:
> Because this query is no longer a simple select. It is translated
> internally into
>
> select min(C) from
> (SELECT * FROM T WHERE (C=1 OR C=2));
Ah, this would indeed explain the slowdown. I was hoping views would translate
into the
Antti Nietosvaara wrote:
> I have a table T with a few million rows. It has a column C with only
> a handful of distinct values for grouping the data. When a user wants
> to access the data my application reads it from a temporary view:
> CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1
I think you should try to rewrite condition to exclude OR like this:
WHERE C >= 1 AND C <= 2. You can even do like this: WHERE C >= 1 AND C
<= 3 AND (C = 1 OR C = 3). I think it will be faster than just ORed
conditions alone.
Pavel
On Thu, Jun 11, 2009 at 5:19 AM, Antti
On Thursday 11 June 2009 11:50:56 Simon Slavin wrote:
> On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote:
> > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
> > where (C=1 OR C=2) will reflect the permissions of the user.
> >
> > There is also an index for C:
> > CREATE
On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote:
> CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
> where (C=1 OR C=2) will reflect the permissions of the user.
>
> There is also an index for C:
> CREATE INDEX idx_C ON T(C);
>
> I have a problem with performance when
Hi all,
I have a table T with a few million rows. It has a column C with only a
handful of distinct values for grouping the data. When a user wants to access
the data my application reads it from a temporary view:
CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
where (C=1 OR
10 matches
Mail list logo
