query mutliple tables slow

[GTR]

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

[Brian Wakem]

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]

[Tigger]

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

Tony

[GTR]

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'?
>
>
>