Обсуждение: ORDER BY time consuming

Hi All,

I got a table with about 4.5 millions rows in it which is connected to
another table with about 60 millions rows which are used as keywords for
searching.

I succeded to create fast queries on the first table that finds a row at
the first table which is connected to up to 4 diffrent keywords at the
second table and LIMIT the result to 12 (I want to allow the surfers of
the site to press back and next to see more products so ill make it with
OFFSET).

I want to be able to order my result by a specific column but when I
insert ORDER BY into the query (and any other query that I tried) it
becomes extremly slow, what can I do to solve this problem?

Thanks in advance,
Ben-Nes Yonatan
Canaan Surfing ltd.
http://www.canaan.net.il

On Sun, Aug 21, 2005 at 12:04:01PM +0200, Ben-Nes Yonatan wrote:
> Hi All,
>
> I got a table with about 4.5 millions rows in it which is connected to
> another table with about 60 millions rows which are used as keywords for
> searching.
>
> I succeded to create fast queries on the first table that finds a row at
> the first table which is connected to up to 4 diffrent keywords at the
> second table and LIMIT the result to 12 (I want to allow the surfers of
> the site to press back and next to see more products so ill make it with
> OFFSET).
>
> I want to be able to order my result by a specific column but when I
> insert ORDER BY into the query (and any other query that I tried) it
> becomes extremly slow, what can I do to solve this problem?

Your question is too generic to answer specifically, but I suspect that
if you use your un-ordered query as a subquery in the FROM clause and
then order that it will work well. IE:

SELECT *
    FROM (SELECT ...) a
    ORDER BY f1, f2, f3
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461

You're also free to set sort_mem (7.4.x) or work_mem (8.0.x) on a per
session basis, so you could try experimenting with raising the value
of those settings during sessions in which your query is running.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 21, 2005, at 12:01 PM, Jim C. Nasby wrote:

> On Sun, Aug 21, 2005 at 12:04:01PM +0200, Ben-Nes Yonatan wrote:
>
>> Hi All,
>>
>> I got a table with about 4.5 millions rows in it which is
>> connected to
>> another table with about 60 millions rows which are used as
>> keywords for
>> searching.
>>
>> I succeded to create fast queries on the first table that finds a
>> row at
>> the first table which is connected to up to 4 diffrent keywords at
>> the
>> second table and LIMIT the result to 12 (I want to allow the
>> surfers of
>> the site to press back and next to see more products so ill make
>> it with
>> OFFSET).
>>
>> I want to be able to order my result by a specific column but when I
>> insert ORDER BY into the query (and any other query that I tried) it
>> becomes extremly slow, what can I do to solve this problem?
>>
>
> Your question is too generic to answer specifically, but I suspect
> that
> if you use your un-ordered query as a subquery in the FROM clause and
> then order that it will work well. IE:
>
> SELECT *
>     FROM (SELECT ...) a
>     ORDER BY f1, f2, f3
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software        http://pervasive.com        512-569-9461