Professional Web Applications Themes

query mutliple tables slow - MySQL

i ran the query below and it takes ages for it to be generated. i ran it on my machine locally aswell as on my web hosting site. is it because there are 7 tables? Can anyone shed any light? Showing rows 0 - 0 (1 total, Query took 22.1602 sec) SQL-query : [Edit] [Explain SQL] [Create PHP Code] SELECT location.locationid, location.nominalmap, location.nominalref, spectrum.spectrumlabel, spectrum.frequency, spectrum.power, clientname.name, licence.managementrightid, licence.licencenumber, licencetype.licencetypeidentifier, licencetype.workingdescription, emission.emission FROM ((((((geographicreference INNER JOIN location ON geographicreference.locationid = location.locationid) INNER JOIN transmitconfiguration ON location.locationid = transmitconfiguration.locationid) INNER JOIN licence ON transmitconfiguration.licenceid = licence.licenceid) INNER JOIN spectrum ON licence.licenceid ...

  1. #1

    Default query mutliple tables slow

    i ran the query below and it takes ages for it to be generated. i ran it
    on my machine locally aswell as on my web hosting site.

    is it because there are 7 tables? Can anyone shed any light?


    Showing rows 0 - 0 (1 total, Query took 22.1602 sec)
    SQL-query : [Edit] [Explain SQL] [Create PHP Code]

    SELECT location.locationid, location.nominalmap, location.nominalref,
    spectrum.spectrumlabel, spectrum.frequency, spectrum.power,
    clientname.name, licence.managementrightid, licence.licencenumber,
    licencetype.licencetypeidentifier, licencetype.workingdescription,
    emission.emission
    FROM ((((((geographicreference INNER JOIN location ON
    geographicreference.locationid = location.locationid) INNER JOIN
    transmitconfiguration ON location.locationid =
    transmitconfiguration.locationid) INNER JOIN licence ON
    transmitconfiguration.licenceid = licence.licenceid) INNER JOIN spectrum
    ON licence.licenceid = spectrum.licenceid) INNER JOIN licencetype ON
    licence.licencetypeid = licencetype.licencetypeid) INNER JOIN clientname
    ON licence.clientid = clientname.clientid) INNER JOIN emission ON
    spectrum.emissionid = emission.emissionid
    WHERE geographicreference.mapnumber='Q10' Or
    geographicreference.mapnumber='Q11' Or
    geographicreference.mapnumber='Q12' Or
    geographicreference.mapnumber='R10' Or
    geographicreference.mapnumber='R11' Or
    geographicreference.mapnumber='R12' Or
    geographicreference.mapnumber='S10' Or
    geographicreference.mapnumber='S11' Or geographicreference.mapnumber='S12'
    HAVING ((location.locationid)=5206)

    cheers
    GTR Guest

  2. #2

    Default Re: query mutliple tables slow

    GTR wrote:
    > i ran the query below and it takes ages for it to be generated. i ran it
    > on my machine locally aswell as on my web hosting site.
    >
    > is it because there are 7 tables? Can anyone shed any light?
    >
    >
    > Showing rows 0 - 0 (1 total, Query took 22.1602 sec)
    > SQL-query : [Edit] [Explain SQL] [Create PHP Code]
    >
    > SELECT location.locationid, location.nominalmap, location.nominalref,
    > spectrum.spectrumlabel, spectrum.frequency, spectrum.power,
    > clientname.name, licence.managementrightid, licence.licencenumber,
    > licencetype.licencetypeidentifier, licencetype.workingdescription,
    > emission.emission
    > FROM ((((((geographicreference INNER JOIN location ON
    > geographicreference.locationid = location.locationid) INNER JOIN
    > transmitconfiguration ON location.locationid =
    > transmitconfiguration.locationid) INNER JOIN licence ON
    > transmitconfiguration.licenceid = licence.licenceid) INNER JOIN spectrum
    > ON licence.licenceid = spectrum.licenceid) INNER JOIN licencetype ON
    > licence.licencetypeid = licencetype.licencetypeid) INNER JOIN clientname
    > ON licence.clientid = clientname.clientid) INNER JOIN emission ON
    > spectrum.emissionid = emission.emissionid
    > WHERE geographicreference.mapnumber='Q10' Or
    > geographicreference.mapnumber='Q11' Or
    > geographicreference.mapnumber='Q12' Or
    > geographicreference.mapnumber='R10' Or
    > geographicreference.mapnumber='R11' Or
    > geographicreference.mapnumber='R12' Or
    > geographicreference.mapnumber='S10' Or
    > geographicreference.mapnumber='S11' Or geographicreference.mapnumber='S12'
    > HAVING ((location.locationid)=5206)

    I don't think the multiple ORs on the same column is optimised very well by
    mysql. Try switching it for WHERE geographicreference.mapnumber IN
    ('Q10','Q11','R10'........) and make sure this column is indexed.

    Why the 'HAVING ((location.locationid)=5206)'? Shouldn't this be 'AND
    location.locationid=5206'?



    --
    Brian Wakem
    Email: [url]http://homepage.ntlworld.com/b.wakem/myemail.png[/url]
    Brian Wakem Guest

  3. #3

    Default Re: query mutliple tables slow

    Try doing an EXPLAIN on the query to see if your indexing correctly.

    Tony

    Tigger Guest

  4. #4

    Default Re: query mutliple tables slow

    i changed the multiple ORs to IN. it worked great. the same query now
    takes 0.0022 sec.

    it works great now thanks

    Showing rows 0 - 0 (1 total, Query took 0.0022 sec)
    SQL-query : [Edit] [Explain SQL] [Create PHP Code]
    SELECT location.locationid, location.nominalmap, location.nominalref,
    spectrum.spectrumlabel, spectrum.frequency, spectrum.power,
    clientname.name, licence.managementrightid, licence.licencenumber,
    licencetype.licencetypeidentifier, licencetype.workingdescription,
    emission.emission
    FROM ((((((geographicreference
    INNER JOIN location ON geographicreference.locationid =
    location.locationid)INNER JOIN transmitconfiguration ON
    location.locationid = transmitconfiguration.locationid)INNER JOIN
    licence ON transmitconfiguration.licenceid = licence.licenceid)
    INNER JOIN spectrum ON licence.licenceid = spectrum.licenceid)
    INNER JOIN licencetype ON licence.licencetypeid =
    licencetype.licencetypeid)INNER JOIN clientname ON licence.clientid =
    clientname.clientid)INNER JOIN emission ON spectrum.emissionid =
    emission.emissionid WHERE geographicreference.mapnumber
    IN ('Q10', 'Q11', 'Q12', 'R10', 'R11', 'R12', 'S10', 'S11', 'S12'
    ) AND (location.locationid) =5206 LIMIT 0 , 30

    Brian Wakem wrote:
    > GTR wrote:
    >
    >
    >>i ran the query below and it takes ages for it to be generated. i ran it
    >>on my machine locally aswell as on my web hosting site.
    >>
    >>is it because there are 7 tables? Can anyone shed any light?
    >>
    >>
    >>Showing rows 0 - 0 (1 total, Query took 22.1602 sec)
    >>SQL-query : [Edit] [Explain SQL] [Create PHP Code]
    >>
    >>SELECT location.locationid, location.nominalmap, location.nominalref,
    >>spectrum.spectrumlabel, spectrum.frequency, spectrum.power,
    >>clientname.name, licence.managementrightid, licence.licencenumber,
    >>licencetype.licencetypeidentifier, licencetype.workingdescription,
    >>emission.emission
    >>FROM ((((((geographicreference INNER JOIN location ON
    >>geographicreference.locationid = location.locationid) INNER JOIN
    >>transmitconfiguration ON location.locationid =
    >>transmitconfiguration.locationid) INNER JOIN licence ON
    >>transmitconfiguration.licenceid = licence.licenceid) INNER JOIN spectrum
    >>ON licence.licenceid = spectrum.licenceid) INNER JOIN licencetype ON
    >>licence.licencetypeid = licencetype.licencetypeid) INNER JOIN clientname
    >>ON licence.clientid = clientname.clientid) INNER JOIN emission ON
    >>spectrum.emissionid = emission.emissionid
    >>WHERE geographicreference.mapnumber='Q10' Or
    >>geographicreference.mapnumber='Q11' Or
    >>geographicreference.mapnumber='Q12' Or
    >>geographicreference.mapnumber='R10' Or
    >>geographicreference.mapnumber='R11' Or
    >>geographicreference.mapnumber='R12' Or
    >>geographicreference.mapnumber='S10' Or
    >>geographicreference.mapnumber='S11' Or geographicreference.mapnumber='S12'
    >>HAVING ((location.locationid)=5206)
    >
    >
    >
    > I don't think the multiple ORs on the same column is optimised very well by
    > mysql. Try switching it for WHERE geographicreference.mapnumber IN
    > ('Q10','Q11','R10'........) and make sure this column is indexed.
    >
    > Why the 'HAVING ((location.locationid)=5206)'? Shouldn't this be 'AND
    > location.locationid=5206'?
    >
    >
    >
    GTR Guest

Similar Threads

  1. v3/Mac - slow, unreliable input handling in tables
    By DulcetTone in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: October 22nd, 08:06 PM
  2. QUERY two tables from different databases
    By WhozitsPop in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 25th, 03:15 PM
  3. Query 5 Tables
    By Speegs in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 18th, 08:11 PM
  4. SQL query with 3 tables.
    By Craig Mason in forum PHP Development
    Replies: 0
    Last Post: May 19th, 01:33 AM
  5. How to Query on multiple tables
    By jt in forum ASP Database
    Replies: 3
    Last Post: May 10th, 10:08 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