Professional Web Applications Themes

Querys seem to take ages - MySQL

I have a mysql server running windows 2003 and mysql 5.0.24. Querys made to the server from the two front end webservers seem to take a long time to complete, if at all. Mysql administrator shows hundreds, sometimes thousands (this is to be expected from the number of hits) of connections, all of which are showing "Select blah from blah where blah". Eventually most of these time out. The tables themselves are small, containing only about 8 fields, and only about 2.5mb of data, so really it should be rocking along. The server itself is an 8 way beast with ...

  1. #1

    Default Querys seem to take ages

    I have a mysql server running windows 2003 and mysql 5.0.24.

    Querys made to the server from the two front end webservers seem to
    take a long time to complete, if at all. Mysql administrator shows
    hundreds, sometimes thousands (this is to be expected from the number
    of hits) of connections, all of which are showing "Select blah from
    blah where blah". Eventually most of these time out.
    The tables themselves are small, containing only about 8 fields, and
    only about 2.5mb of data, so really it should be rocking along.

    The server itself is an 8 way beast with 8GB of ram, only 1.5GB of
    which is in use at any one tme, and the processors are more or less
    idling.

    I'm at a loss to explain why mysql is taking so long to return queries,
    if at all.

    Can anyone help? Is there anything to look for as to a definitive
    reason for the slowness?

    Olly

    Oliver Guest

  2. #2

    Default Re: Querys seem to take ages

    What does the EXPLAIN command say about these queries?

    Oliver Marshall wrote: 
    Dikkie Guest

  3. #3

    Default Re: Querys seem to take ages

    EXPLAIN select occurences from db.table_events where
    (event='2_raceTrainingLevel' and param='1' and subset='nl');
    returned - real quickly..


    "id","select_type","table","type","possible_keys", "key","key_len","ref","rows","Extra"
    1,"SIMPLE","table_events","ALL","","","","",4940," Using where"

    Oliver Guest

  4. #4

    Default Re: Querys seem to take ages

    So it seems that there are no keys defined that can be used. This means
    that MySQL has no alternative but to go through _all_ the records of the
    table for each query. No wonder this takes a long time, especially if
    new queries are issued while another is still being processed.

    For this query, you would add an index on the 3 fields used in the WHERE
    clause. But this might be overkill if there are a lot of different seach
    actions on different fields. In that case, is doesn't hurt to have
    single-field indexes on the fields that are most often searched.

    Just try it. You can remove an index later if it doesn't work out.

    Best regards

    Oliver Marshall wrote: 
    Dikkie Guest

  5. #5

    Default Re: Querys seem to take ages

    Thanks. I will give it a try.

    Olly

    Dikkie Dik wrote: [/ref]

    Oliver Guest

Similar Threads

  1. Problem in Querys Dynamics
    By venon69 in forum Coldfusion Database Access
    Replies: 5
    Last Post: September 26th, 01:16 PM
  2. Order By or sorting nested querys
    By ICI-MASA in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 8th, 03:39 PM
  3. Trouble with querys, joins and dates
    By sky4est in forum Macromedia ColdFusion
    Replies: 4
    Last Post: May 11th, 09:19 PM
  4. searching multiple ages
    By John Vinson in forum Microsoft Access
    Replies: 0
    Last Post: July 19th, 01:17 AM

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