Professional Web Applications Themes

Slow query with last record included - MySQL

Hello, I have a strange problem witha very easy query! I have a MyIsam table, 150000 rows with company information and other values for 50 fields total. There is a primary key (code), an index on "companyname", table has been yzed and optimized. I want to show 50 rows on every web page, and I have included button for moving to previous, next, first and last page. Query is: SELECT * FROM contacts ORDER BY companyname LIMIT nnnn,50 It works fine, expect for the LAST page feature... the query took more than 30 seconds with hard drive 100% loaded! The ...

  1. #1

    Default Slow query with last record included

    Hello,
    I have a strange problem witha very easy query!
    I have a MyIsam table, 150000 rows with company information and other values
    for 50 fields total.
    There is a primary key (code), an index on "companyname", table has been
    yzed and optimized.

    I want to show 50 rows on every web page, and I have included button for
    moving to previous, next, first and last page.
    Query is:

    SELECT * FROM contacts ORDER BY companyname LIMIT nnnn,50

    It works fine, expect for the LAST page feature... the query took more than
    30 seconds with hard drive 100% loaded!
    The values passed to the LIMIT are correct, as the query ends and show the
    right informations.

    Example:

    Select * from contacts order by companyname LIMIT 0,50 took 1 sec
    Select * from contacts order by companyname LIMIT 130000,50 took 1 sec
    Select * from contacts order by companyname LIMIT 149950,50 took 30 secs
    Select * from contacts order by companyname LIMIT 149950,49 took 1 sec
    (!!!!!!)

    The problem is related to the last record of the table, if I limit the query
    to the penultimate record it ends in 1 second!!
    I really can't explain this...the platform used is Mysql 5.0.18 on Win32
    (also with 4.0 same problem)

    Thanks for your help!

    Marco


    BadWolf Guest

  2. #2

    Default Re: Slow query with last record included

    Does it help to rewrite the query for the last page to:

    SELECT * FROM contacts ORDER BY companyname DESC LIMIT 0,50

    and displaying the results in opposite order?

    Best regards,

    Willem Bogaerts

    BadWolf wrote:
    > Hello,
    > I have a strange problem witha very easy query!
    > I have a MyIsam table, 150000 rows with company information and other values
    > for 50 fields total.
    > There is a primary key (code), an index on "companyname", table has been
    > yzed and optimized.
    >
    > I want to show 50 rows on every web page, and I have included button for
    > moving to previous, next, first and last page.
    > Query is:
    >
    > SELECT * FROM contacts ORDER BY companyname LIMIT nnnn,50
    >
    > It works fine, expect for the LAST page feature... the query took more than
    > 30 seconds with hard drive 100% loaded!
    > The values passed to the LIMIT are correct, as the query ends and show the
    > right informations.
    >
    > Example:
    >
    > Select * from contacts order by companyname LIMIT 0,50 took 1 sec
    > Select * from contacts order by companyname LIMIT 130000,50 took 1 sec
    > Select * from contacts order by companyname LIMIT 149950,50 took 30 secs
    > Select * from contacts order by companyname LIMIT 149950,49 took 1 sec
    > (!!!!!!)
    >
    > The problem is related to the last record of the table, if I limit the query
    > to the penultimate record it ends in 1 second!!
    > I really can't explain this...the platform used is Mysql 5.0.18 on Win32
    > (also with 4.0 same problem)
    >
    > Thanks for your help!
    >
    > Marco
    >
    >
    Dikkie Dik Guest

  3. #3

    Default Re: Slow query with last record included

    "Dikkie Dik" <nospamnospam.org> ha scritto nel messaggio
    news:c38c9$440f4468$57d40752$32311news.versatel.n l...
    > Does it help to rewrite the query for the last page to:
    >
    > SELECT * FROM contacts ORDER BY companyname DESC LIMIT 0,50
    >
    > and displaying the results in opposite order?
    It helps about the speed issue, but will display the record in reverse
    order!
    By the way, I think I solved my problem forcing Mysql to use the index
    created on "companyname".

    I don't know why, but also the EXPLAIN command confirmed that when the query
    involve the last record the index is not used, and table is filesorted.
    Even the query "SELECT * FROM contacts ORDER BY companyname" (without LIMIT)
    is not using the index, why??



    Thanks and best regards,
    Marco



    BadWolf Guest

Similar Threads

  1. Help in optimizing a slow query...
    By DDJ in forum MySQL
    Replies: 1
    Last Post: May 13th, 06:13 PM
  2. Slow MySql query
    By jack in forum PHP Development
    Replies: 2
    Last Post: August 31st, 08:44 PM
  3. Occasional slow query
    By AK in forum IBM DB2
    Replies: 2
    Last Post: August 19th, 04:20 PM
  4. Adding a new record when the record source is a query.
    By Andy in forum Microsoft Access
    Replies: 0
    Last Post: July 29th, 01:41 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