Professional Web Applications Themes

Query optimization suggestions - MySQL

Hi all, I have a table like that: articles { cat INT(10) UNSIGNED id INT(10) UNSIGNED aorder INT(10) UNSIGNED lastupdated INT(10) UNSIGNED } I want to query something like: SELECT * FROM articles WHERE cat = 9 AND aorder >= 0 ORDER BY aorder DESC, lastupdated DESC LIMIT 13095, 10 the query is quite slow when number of records in the articles say, more than 1M row currently, index = (cat, aorder, lastupdated) any suggestion to improve or re-design the table? thanks....

  1. #1

    Default Query optimization suggestions

    Hi all,

    I have a table like that:


    articles {

    cat INT(10) UNSIGNED
    id INT(10) UNSIGNED
    aorder INT(10) UNSIGNED
    lastupdated INT(10) UNSIGNED
    }


    I want to query something like:

    SELECT * FROM articles
    WHERE cat = 9 AND aorder >= 0
    ORDER BY aorder DESC, lastupdated DESC
    LIMIT 13095, 10

    the query is quite slow when number of records in the articles say,
    more than 1M row

    currently, index = (cat, aorder, lastupdated)

    any suggestion to improve or re-design the table?

    thanks.

    howa Guest

  2. #2

    Default Re: Query optimization suggestions


    howa wrote: 
    Does an explain say that the index is being used for the sort?

    If not try ORDER BY cat DESC, aorder DESC, lastupdated DESC; in order
    (no pun intended) to help the optimiser to realise what it should be
    doing.

    Captain Guest

  3. #3

    Default Re: Query optimization suggestions


    Captain Paralytic gDG
     
    > Does an explain say that the index is being used for the sort?
    >
    > If not try ORDER BY cat DESC, aorder DESC, lastupdated DESC; in order
    > (no pun intended) to help the optimiser to realise what it should be
    > doing.[/ref]

    the optimizer said the index - (cat, aorder, lastupdated) is being
    used, rows involved is around 60K (out of 2M rows) - becasue actually
    the number of rows satisifed the conidition is really 60K...

    but i only want to take 15 rows out of them (due to paging)

    are there anyway to rewrite the query can improve the result?

    howa Guest

Similar Threads

  1. need help with query optimization
    By Bosconian in forum MySQL
    Replies: 8
    Last Post: October 10th, 10:30 PM
  2. Assistance with Query Optimization
    By GS in forum MySQL
    Replies: 4
    Last Post: September 13th, 09:27 PM
  3. Replies: 2
    Last Post: March 2nd, 04:17 PM
  4. Query Optimization ... Is this all I can get??
    By Anthony in forum Informix
    Replies: 7
    Last Post: January 16th, 07:51 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