Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    slovakia
    Posts
    3

    Red face Unanswered: optimized select

    Hi,

    please help,

    I have simple table with three separate indexex on it:

    on columns id, dst_no and status;
    column id is primary key;

    Selects like

    select * from table where status='R' and dst_no='7325' LIMIT 1

    are really fast (100 selects in less than one second)

    as soon I have

    select * from table where status='R' and dst_no='7325' order by id LIMIT 1

    or

    select * from table WHERE status = 'R' AND dst_no = '7325' AND id=(select min(id) from table)


    it takes a lot of time really (100 selects in 20 seconds)

    How to force the order by id without using 'order by' ????
    If I ommit order by, it is not sorted by id, even id is primary key;

    simply:I need to get one record with condition on status and on dst_no with minimum id very fastly


    Any hints ?

    thank you very much

  2. #2
    Join Date
    Oct 2003
    Posts
    33
    You can't do this without using the "order by" keyword. Using index allow the db to find the row faster, but it doesn't order it.

    without changing the structure of your table, te only think you can do to try to optimize you request is tu use the vacuum request, so it will try to order th indexes better according to the most often used requests.

  3. #3
    Join Date
    Oct 2003
    Location
    slovakia
    Posts
    3

    Thumbs up

    Originally posted by le_mogwai
    You can't do this without using the "order by" keyword. Using index allow the db to find the row faster, but it doesn't order it.

    without changing the structure of your table, te only think you can do to try to optimize you request is tu use the vacuum request, so it will try to order th indexes better according to the most often used requests.
    thanks for reply;

    index allow the db to find the row faster, because the are ordered in index (using btree for example);

    anyway, I have got it - if I make composite index on (status, dst_no, id)
    and I will run select with where condition:

    WHERE status=something AND dst_no=something LIMIT 1

    I will get record according the condition with minumum id, because this
    composite index is used in such select

    radoslav

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •