Professional Web Applications Themes

need help with query optimization - MySQL

I need some assistance in optimizing the following query: SELECT DISTINCT c.* FROM cases c INNER JOIN profile p ON p.uid = c.uid LEFT JOIN casespecialty cs ON c.caseid = cs.caseid LEFT JOIN examspecialty es ON c.caseid = es.caseid WHERE (cs.uid = 103 OR es.uid = 103) I know the source of the problem is with the LEFT JOINs. They both reference the same caseid in the cases table. If only one LEFT JOIN is included then the query returns the recordset almost immediately. If both are included the query takes more than 7 seconds (which is too slow for ...

  1. #1

    Default need help with query optimization

    I need some assistance in optimizing the following query:

    SELECT DISTINCT c.*
    FROM cases c
    INNER JOIN profile p ON p.uid = c.uid
    LEFT JOIN casespecialty cs ON c.caseid = cs.caseid
    LEFT JOIN examspecialty es ON c.caseid = es.caseid
    WHERE (cs.uid = 103 OR es.uid = 103)

    I know the source of the problem is with the LEFT JOINs. They both reference
    the same caseid in the cases table. If only one LEFT JOIN is included then
    the query returns the recordset almost immediately. If both are included the
    query takes more than 7 seconds (which is too slow for a query that's used
    continually.) Both are required because related records can exist in both
    tables (casespecialty and examspecialty.)

    Any suggestions would be greatly appreciated.


    Bosconian Guest

  2. #2

    Default Re: need help with query optimization

    What indexes do you have on these tables?

    Have you tried doing an EXPLAIN?

    Tigger

    Bosconian wrote: 

    Tigger Guest

  3. #3

    Default Re: need help with query optimization

    "Tigger" <tv> wrote in message
    news:googlegroups.com... 
    >[/ref]

    Thanks for your reply.

    You must have read my mind because this my post I've been spending some time
    learning how to better optimize my table. I have used at EXPLAIN, but I'm
    not quite sure what to make of the output.

    My assumption is that there isn't anything fundamentally flawed about my
    query--I just need to optimize the indexing. I'll keep investigating.


    Bosconian Guest

  4. #4

    Default Re: need help with query optimization

    If you want to post the expain output we might be able to give you some
    pointers.


    Bosconian wrote: 
    > >[/ref]
    >
    > Thanks for your reply.
    >
    > You must have read my mind because this my post I've been spending some time
    > learning how to better optimize my table. I have used at EXPLAIN, but I'm
    > not quite sure what to make of the output.
    >
    > My assumption is that there isn't anything fundamentally flawed about my
    > query--I just need to optimize the indexing. I'll keep investigating.[/ref]

    Tigger Guest

  5. #5

    Default Re: need help with query optimization

    "Tigger" <tv> wrote in message
    news:googlegroups.com... 
    >>
    >> Thanks for your reply.
    >>
    >> You must have read my mind because this my post I've been spending some
    >> time
    >> learning how to better optimize my table. I have used at EXPLAIN, but I'm
    >> not quite sure what to make of the output.
    >>
    >> My assumption is that there isn't anything fundamentally flawed about my
    >> query--I just need to optimize the indexing. I'll keep investigating.[/ref]
    >[/ref]

    Some pointers would be much appreciated.

    id select_type table type possible_keys key key_len
    ref rows Extra
    1 SIMPLE p ALL NULL NULL NULL NULL
    520 Using temporary
    1 SIMPLE c ALL NULL NULL NULL NULL
    958 Using where
    1 SIMPLE cs ALL NULL NULL NULL NULL
    1179 Distinct
    1 SIMPLE es ALL NULL NULL NULL NULL
    908 Using where; Distinct


    Bosconian Guest

  6. #6

    Default Re: need help with query optimization

    Bosconian,

    This indicates you have no indexes/keys so it is using very inefficient
    methods to find the joined information. Such as searching the whole
    joined table for every row of the primary table (This is indicated in
    the large number in the rows column of the explain). This gets very
    slow when there are multiple levels of joins.

    I would first make sure all your uid fields (which I think are your
    "primary keys") are made primary keys and indexed.

    Then make sure your "foreign keys" are indexed. these are the caseid
    fields in your c, cs and es tables. Foreign keys are fields used to
    reference rows in other tables i.e. the fields commonly used in joins
    and generally should be indexed because of this.

    Check if the speed is better and post the new EXPLAIN results. We want
    to see the key field have values and the rows field contain smaller
    numbers.

    Tigger

    Bosconian wrote: 

    Tigger Guest

  7. #7

    Default Re: need help with query optimization

    "Tigger" <tv> wrote in message
    news:googlegroups.com... 

    Hi Tigger,

    Thank you very much for your detailed reply.

    I was embarrassed to find that nothing beyond the primary keys for each
    table was indexed.

    I have taken your advice and indexed all foreign keys and immediately saw a
    HUGE performance boost.

    I have included the most recent EXPLAIN results. What do you make of the
    first line?


    id select_type table type possible_keys key
    key_len ref rows Extra
    1 SIMPLE c ALL uid NULL
    NULL NULL 1041 Using temporary
    1 SIMPLE p ref uid uid
    4 c.uid 1 Using index; Distinct
    1 SIMPLE cs ref caseid caseid
    4 c.caseid 1 Distinct
    1 SIMPLE es ref caseid caseid
    4 c.caseid 1 Using where; Distinct


    Bosconian Guest

  8. #8

    Default Re: need help with query optimization

    Because you are not filtering your c table by anything it has to pull
    in the whole table to start with.

    You might be able to improve things if you can switch the main table to
    the cs or es table. As these are filtered in the WHERE you will limit
    the rows quite significantly from the start.

    Tigger

    Bosconian wrote: 
    >
    > Hi Tigger,
    >
    > Thank you very much for your detailed reply.
    >
    > I was embarrassed to find that nothing beyond the primary keys for each
    > table was indexed.
    >
    > I have taken your advice and indexed all foreign keys and immediately saw a
    > HUGE performance boost.
    >
    > I have included the most recent EXPLAIN results. What do you make of the
    > first line?
    >
    >
    > id select_type table type possible_keys key
    > key_len ref rows Extra
    > 1 SIMPLE c ALL uid NULL
    > NULL NULL 1041 Using temporary
    > 1 SIMPLE p ref uid uid
    > 4 c.uid 1 Using index; Distinct
    > 1 SIMPLE cs ref caseid caseid
    > 4 c.caseid 1 Distinct
    > 1 SIMPLE es ref caseid caseid
    > 4 c.caseid 1 Using where; Distinct[/ref]

    Tigger Guest

  9. #9

    Default Re: need help with query optimization

    "Tigger" <tv> wrote in message
    news:googlegroups.com... 

    OK, I get it. Switching the main table doesn't seem to make a difference in
    this case, but the information is invaluable.

    Thanks very much for taking the time to explain EXPLAIN. :-)


    Bosconian Guest

Similar Threads

  1. Assistance with Query Optimization
    By GS in forum MySQL
    Replies: 4
    Last Post: September 13th, 09:27 PM
  2. 3D optimization
    By DaveRaith in forum Macromedia Director 3D
    Replies: 6
    Last Post: March 20th, 02:06 PM
  3. Looking for Optimization
    By Thomas Neumann in forum PHP Development
    Replies: 1
    Last Post: October 15th, 06:43 AM
  4. table space impact on query optimization
    By xixi in forum IBM DB2
    Replies: 2
    Last Post: August 6th, 04:16 PM
  5. Replies: 3
    Last Post: July 11th, 09:08 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