Professional Web Applications Themes

query execute in 1.5h - why is my query so long time execute? - MySQL

I make query: SELECT c.label FROM commission as c LEFT JOIN note as n ON (c.id = n.commission_id ) It is 95522 rows fetched in 92:00 (about 1.5h ) (Commission have got 23422 rows ) (Note have got 93313 rows. ) Why is my query so long time execute? What is the reason of this behavior? How can i cut down the time of execute my query? The same query in MS-ACCESS fetched less then 1 minute on the same computer. I have got AMD Athlon(TM) XP 1600+ 1050.235 MHz, 0,7 GB RAM. MySQL Client Version 5.0.19; Linux 2.6.15-28-386 Below ...

  1. #1

    Default query execute in 1.5h - why is my query so long time execute?

    I make query:

    SELECT c.label
    FROM commission as c
    LEFT JOIN note as n ON (c.id = n.commission_id )

    It is 95522 rows fetched in 92:00 (about 1.5h )
    (Commission have got 23422 rows )
    (Note have got 93313 rows. )

    Why is my query so long time execute?
    What is the reason of this behavior?
    How can i cut down the time of execute my query?

    The same query in MS-ACCESS fetched less then 1 minute on the same
    computer.

    I have got AMD Athlon(TM) XP 1600+ 1050.235 MHz, 0,7 GB RAM.
    MySQL Client Version 5.0.19; Linux 2.6.15-28-386



    Below I show example of my tables:

    1)
    +------------------+
    |commission |
    +-----+-----------+
    | id | label |
    +----+------------+
    | 1 | 1/1/NY |
    | 2 | 2/1/NY |
    | 3 | 3/1/NY |
    | 4 | 4/1/NC |
    | 5 | 5/1/NY |
    .....
    .....
    .....
    | 23422 | 23422/WA |
    +----------+-----------------+


    commission.id is auto_increment primary key
    This table have got 23422 rows.

    2) example of my table
    +----------------------------+
    |note |
    +----+----------------------+
    | id | commission_id |
    +----+----------------------+
    | 1 | 1 |
    | 2 | 1 |
    | 3 | 1 |
    | 4 | 4 |
    | 5 | 4 |
    .....
    .....
    .....
    | 93313 | 1000 |
    +---------+---------------+


    note.id is auto_increment primary key
    note.commission_id is foreign key
    This table have got 93313 rows.




    Best regards
    Robert
    For any help I am very appreciated

    robert.szczepanski@gmail.com Guest

  2. #2

    Default Re: query execute in 1.5h - why is my query so long time execute?

    com wrote: 

    Do you have an index on note.commission_id?

    What do you get if you EXPLAIN your query?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: query execute in 1.5h - why is my query so long time execute?

    Hello everybody!

    Thank You for Your help.

    I am adding index to note.commission_id i.e:
    KEY 'commission_id' ('commission_id')
    and now my query fetching in less then 1 minute.

    I am altering query in MS-ACCESS to MySql and
    therefore I need this query. For convenience my problem
    I am not showing complete this query.

    Best regards
    Robert.

    robert.szczepanski@gmail.com Guest

  4. #4

    Default Re: query execute in 1.5h - why is my query so long time execute?

    com wrote: 

    There is no way we can give you good suggestions based on incomplete
    information.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: query execute in 1.5h - why is my query so long time execute?

    On Apr 8, 3:38 am, com wrote: 

    Why are you joining to `note`?

    Could your speed issue be in the processing of such a large number of
    rows and not the speed of the query.


    Tigger Guest

Similar Threads

  1. Execute Query on trigger
    By Kryss2099 in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 31st, 10:50 PM
  2. How to execute 2 sql commands in one sql query?
    By Vlad in forum ASP Database
    Replies: 8
    Last Post: June 18th, 07:28 AM
  3. How to execute an ADO query asynchronously?
    By kibokochui in forum ASP Database
    Replies: 5
    Last Post: January 26th, 05:55 PM
  4. Replies: 5
    Last Post: September 17th, 09:27 PM
  5. Execute Query in Access from an ASP Form?
    By JL in forum ASP Components
    Replies: 11
    Last Post: July 4th, 09:52 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