slow query

Hi. Hope this is the right place to ask.

I have a table "info" with columns "path", "field" and "value" which
identify properties of some objects. The objects are identified with the
"path" column, while field and value contain the properties.

Now I want to find all objects (paths) which

- either have field="year" and value="2008"
- or do not have the field="year" at all but have the field
"creation-date" with value "2008%".

I also want to sort the results by the value of the "sort-key" field.

I use this query:

SELECT DISTINCT a.path FROM info AS a, info AS b, info AS c
WHERE
(( a.field="year" AND a.value=2008)
OR (a.field="creation-date" and a.value like "2008%"
AND a.path=c.path AND NOT c.field="year" )
)
AND a.path=b.path AND b.field="sort-key"
ORDER BY b.value;

but this turns out to be somewhat slow (5 sec on a table with 20000
rows). Is there a better way to achieve the task?

E.

On 15 Apr, 13:22, ?manu* <paol...@NO.math.unifi.SPAM.it> wrote:
> Hi. Hope this is the right place to ask.
>
> I have a table "info" with columns "path", "field" and "value" which
> identify properties of some objects. The objects are identified with the
> "path" column, while field and value contain the properties.
>
> Now I want to find all objects (paths) which
>
> - either have field="year" and value="2008"
> - or do not have the field="year" at all but have the field
> "creation-date" with value "2008%".
>
> I also want to sort the results by the value of the "sort-key" field.
>
> I use this query:
>
> SELECT DISTINCT a.path FROM info AS a, info AS b, info AS c
> WHERE
> (( a.field="year" AND a.value=2008)
> OR (a.field="creation-date" and a.value like "2008%"
> AND a.path=c.path AND NOT c.field="year" )
> )
> AND a.path=b.path AND b.field="sort-key"
> ORDER BY b.value;
>
> but this turns out to be somewhat slow (5 sec on a table with 20000
> rows). Is there a better way to achieve the task?
>
> E.

I would re-write this as implicit JOINs. It becomes much clearer what
is happening.

This is a terrible design and what's more, you are mixing up your
terms terribly. There is no "sort-key" field, there is only a field
with a value of "sort-key".

What indexes do you have built on this table?

Captain Paralytic wrote:
> I would re-write this as implicit JOINs. It becomes much clearer what
> is happening.

I don't know what an "implicit" JOIN is, I will investigate...

> What indexes do you have built on this table?

Only the "path" has an index.

E.

On 15 Apr, 14:54, ?manu* <paol...@NO.math.unifi.SPAM.it> wrote:
> Captain Paralytic wrote:
> > I would re-write this as implicit JOINs. It becomes much clearer what
> > is happening.
>
> I don't know what an "implicit" JOIN is, I will investigate...
>
> > What indexes do you have built on this table?
>
> Only the "path" has an index.
>
> E.

Oops, I meant "exlicit" JOINs
You are using comma joins with the join conditions in the WHERE
clauses.
Explicit JOINs use the JOIN (or LEFT JOIN or RIGHT JOIN, ....) keyword
and have the JOIN conditions in an ON or USING clause.

Indexes are your problem. This is a really bad design made worse by a
total lack of indexes. Based on what I think the query is doing (it
would be a lot clearer written with explicit JOINs) I would create a
composite index on path-field-value

Captain Paralytic wrote:
> On 15 Apr, 14:54, ?manu* <paol...@NO.math.unifi.SPAM.it> wrote:
>
>>Captain Paralytic wrote:
>>
>>>I would re-write this as implicit JOINs. It becomes much clearer what
>>>is happening.
>>
>>I don't know what an "implicit" JOIN is, I will investigate...
>>
>>
>>>What indexes do you have built on this table?
>>
>>Only the "path" has an index.
>
> Oops, I meant "exlicit" JOINs
> You are using comma joins with the join conditions in the WHERE
> clauses.
> Explicit JOINs use the JOIN (or LEFT JOIN or RIGHT JOIN, ....) keyword
> and have the JOIN conditions in an ON or USING clause.

Ok, I will investigate these (I'm not expert with SQL as you can see).

> Indexes are your problem.
> This is a really bad design made worse by a
> total lack of indexes.


The point with this design is that I have objects with named properties.
The number of different properties is not so large (maybe 20) so I could
also make a column for each property. The point is that some properties
could have multiple values. How can I deal with this? Maybe I should
make a table for every property?

> Based on what I think the query is doing (it
> would be a lot clearer written with explicit JOINs) I would create a
> composite index on path-field-value

I will try.

E.

On 15 Apr, 16:04, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 15 Apr, 14:54, ?manu* <paol...@NO.math.unifi.SPAM.it> wrote:
>
> > Captain Paralytic wrote:
> > > I would re-write this as implicit JOINs. It becomes much clearer what
> > > is happening.
>
> > I don't know what an "implicit" JOIN is, I will investigate...
>
> > > What indexes do you have built on this table?
>
> > Only the "path" has an index.
>
> > E.
>
> Oops, I meant "exlicit" JOINs
> You are using comma joins with the join conditions in the WHERE
> clauses.
> Explicit JOINs use the JOIN (or LEFT JOIN or RIGHT JOIN, ....) keyword
> and have the JOIN conditions in an ON or USING clause.
>
> Indexes are your problem. This is a really bad design made worse by a
> total lack of indexes. Based on what I think the query is doing (it
> would be a lot clearer written with explicit JOINs) I would create a
> composite index on path-field-value

'Oops, I meant "exlicit" JOINs'

Are you sure that's what you meant? ;-)

On 15 Apr, 15:33, strawberry <zac.ca...@gmail.com> wrote:
> On 15 Apr, 16:04, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
> > On 15 Apr, 14:54, ?manu* <paol...@NO.math.unifi.SPAM.it> wrote:
>
> > > Captain Paralytic wrote:
> > > > I would re-write this as implicit JOINs. It becomes much clearer what
> > > > is happening.
>
> > > I don't know what an "implicit" JOIN is, I will investigate...
>
> > > > What indexes do you have built on this table?
>
> > > Only the "path" has an index.
>
> > > E.
>
> > Oops, I meant "exlicit" JOINs
> > You are using comma joins with the join conditions in the WHERE
> > clauses.
> > Explicit JOINs use the JOIN (or LEFT JOIN or RIGHT JOIN, ....) keyword
> > and have the JOIN conditions in an ON or USING clause.
>
> > Indexes are your problem. This is a really bad design made worse by a
> > total lack of indexes. Based on what I think the query is doing (it
> > would be a lot clearer written with explicit JOINs) I would create a
> > composite index on path-field-value
>
> 'Oops, I meant "exlicit" JOINs'
>
> Are you sure that's what you meant? ;-)

ARRRRGGGGHHH!!!!!

Eye don't no watt aye meant!

Captain Paralytic wrote:
> On 15 Apr, 14:54, ?manu* <paol...@NO.math.unifi.SPAM.it> wrote:
>
>>Captain Paralytic wrote:
>>
>>>I would re-write this as implicit JOINs. It becomes much clearer what
>>>is happening.
>>
>>I don't know what an "implicit" JOIN is, I will investigate...
>>
>>
>>>What indexes do you have built on this table?
>>
>>Only the "path" has an index.
>>
>>E.
>
>
> Oops, I meant "exlicit" JOINs

I try this

SELECT DISTINCT a.path FROM info AS a CROSS JOIN info AS b CROSS JOIN
info AS c ON a.path=b.path AND a.path=c.path WHERE ((a.field="year" AND
a.value=2008) OR (a.field="creation-date" AND a.value LIKE "2008%" AND
NOT c.field="year") ) AND b.field="sort-key" ORDER BY b.value;

....and yes! It is much more efficient. From 5 sec to 0.4 sec!

> Indexes are your problem. This is a really bad design made worse by a
> total lack of indexes. Based on what I think the query is doing (it
> would be a lot clearer written with explicit JOINs) I would create a
> composite index on path-field-value

I also tried to add indexes but this was not useful.

E.

?manu* wrote:
> Captain Paralytic wrote:
>> On 15 Apr, 14:54, ?manu* <paol...@NO.math.unifi.SPAM.it> wrote:
>>
>>> Captain Paralytic wrote:
>>>
>>>> I would re-write this as implicit JOINs. It becomes much clearer what
>>>> is happening.
>>>
>>> I don't know what an "implicit" JOIN is, I will investigate...
>>>
>>>
>>>> What indexes do you have built on this table?
>>>
>>> Only the "path" has an index.
>>
>> Oops, I meant "exlicit" JOINs
>> You are using comma joins with the join conditions in the WHERE
>> clauses.
>> Explicit JOINs use the JOIN (or LEFT JOIN or RIGHT JOIN, ....) keyword
>> and have the JOIN conditions in an ON or USING clause.
>
> Ok, I will investigate these (I'm not expert with SQL as you can see).
>
>> Indexes are your problem. This is a really bad design made worse by a
>> total lack of indexes.
>
>
> The point with this design is that I have objects with named properties.
> The number of different properties is not so large (maybe 20) so I could
> also make a column for each property. The point is that some properties
> could have multiple values. How can I deal with this? Maybe I should
> make a table for every property?
>
>> Based on what I think the query is doing (it
>> would be a lot clearer written with explicit JOINs) I would create a
>> composite index on path-field-value
>
> I will try.
>
> E.
>

No, rather, google for "database normalization". Create your tables in
third normal form (3NF) and you will be OK.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================