Professional Web Applications Themes

Select last entries for many items take forever.... - MySQL

Hi, I'm trying to get the last inserted record for items (cNo) in a table. SELECT csp.cNo, CONVERT_TZ(posDateTime,'GMT','SYSTEM') as posDateTime FROM c_pos csp WHERE c_posNO=( SELECT c_posNO as CS FROM c_pos WHERE cNO=csp.cNo ORDER BY c_posNo DESC Limit 1 ); This query do what I want but it take in 5sec to execute and I just have 1300 records in my table... The table will have something like 10000 records in a near future. What can I do ? Is there a alternative to the "ORDER BY c_posNo DESC Limit 1" to get the last record ? Thanks !!...

  1. #1

    Default Select last entries for many items take forever....

    Hi, I'm trying to get the last inserted record for items (cNo) in a
    table.

    SELECT csp.cNo, CONVERT_TZ(posDateTime,'GMT','SYSTEM') as posDateTime
    FROM c_pos csp
    WHERE c_posNO=(
    SELECT c_posNO as CS FROM c_pos WHERE cNO=csp.cNo ORDER BY c_posNo DESC
    Limit 1
    );

    This query do what I want but it take in 5sec to execute and I just
    have 1300 records in my table...
    The table will have something like 10000 records in a near future.

    What can I do ? Is there a alternative to the "ORDER BY c_posNo DESC
    Limit 1" to get the last record ?

    Thanks !!

    AlexHWGUY Guest

  2. #2

    Default Re: Select last entries for many items take forever....


    AlexHWGUY wrote: 

    It looks a lot to me like your syntax may be a little ed up and
    the database is probably doing about 3000 times more work than it needs
    to. I haven't looked in great detail myself yet, but I will do shortly,
    and see if I can make any suggestions.

    Daz Guest

  3. #3

    Default Re: Select last entries for many items take forever....


    AlexHWGUY wrote: 

    Perhaps I am missing the bigger picture here, but is there any reason
    why you can't use:

    SELECT
    cNo,
    CONVERT_TZ(posDateTime,'GMT','SYSTEM') as posDateTime
    FROM
    c_pos csp
    ORDER BY
    c_posNo DESC
    LIMIT
    1
    );
    This will sort the table in descending order, and select the one from
    the top.

    Without seeing the table you are trying to get the data from, and the
    datatypes for those columns, I can't really be any more help.

    All the best!

    Daz

    Daz Guest

  4. #4

    Default Re: Select last entries for many items take forever....

    "AlexHWGUY" <com> wrote in news:1160744007.259125.248170
    k70g2000cwa.googlegroups.com:
     

    Type "EXPLAIN" before the query and you'll see what the database has to do
    in order to retrieve the results. As others have suggested, perhaps the
    query itself is pulling way too much data; perhaps indexes aren't being
    used. You might want to ask yourself if a subquery is really necessary -
    perhaps a JOIN would be much better (ask yourself how you would have solved
    this problem in MySQL 4).

    EXPLAIN will help pinpoint why your query is taking so long.
    Good Guest

  5. #5

    Default Re: Select last entries for many items take forever....

    Thanks for your replys

    I finally split the query in 2 and used a While to select le last
    record of each distinct cNo.

    Daz, your query only select the last record of the table and not a last
    record for each cNo.

    Good Man, I will try with EXPLAIN.

    Thanks again !




    On Oct 15, 7:34 pm, Good Man <com> wrote: 




    > in order to retrieve the results. As others have suggested, perhaps the
    > query itself is pulling way too much data; perhaps indexes aren't being
    > used. You might want to ask yourself if a subquery is really necessary -
    > perhaps a JOIN would be much better (ask yourself how you would have solved
    > this problem in MySQL 4).
    >
    > EXPLAIN will help pinpoint why your query is taking so long.[/ref]

    AlexHWGUY Guest

  6. #6

    Default Re: Select last entries for many items take forever....


    AlexHWGUY wrote: 


    Alex, GoodMan has given you the pointer from a trick hat I passed on to
    him recently. You need to use a query similar to that in this thread:

    http://groups.google.co.uk/group/alt.php.sql/browse_frm/thread/456528317c40d8d1/ed022077f46cdec9?lnk=gst&q=strawberry&rnum=3#ed022 077f46cdec9

    Avoid subqueries wherever possible. Using the IS NULL with LEFT JOIN
    trick will give you a very efficient single query to get your result.

    Captain Guest

Similar Threads

  1. select count(*) from <table> taking FOREVER
    By Fan Ruo Xin in forum IBM DB2
    Replies: 10
    Last Post: June 6th, 05:24 PM
  2. select last 4 entries
    By johnbennett in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: July 11th, 07:01 PM
  3. Select a list of items into an aliased field when doinga select
    By ehaemmerle in forum Coldfusion Database Access
    Replies: 3
    Last Post: March 18th, 10:49 PM
  4. SELECT COUNT, but only on the last 100 entries
    By Bob in forum ASP Database
    Replies: 6
    Last Post: August 20th, 11:43 AM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139