[eluser]Unknown[/eluser]
My app uses this sql ...
SELECT * FROM (( SELECT DISTINCT c.companyname,c.tradingas,a.quarantined,c.companyid,a.address1,a.city,a.postcode,a.region,c.origin_dsn,d.title,d.firstname,d.surname,d.position,a.telephoneno,a.companyemail,addresstypes,markets FROM companies c join addresses a on c.companyid = a.companyid join contacts d on c.companyid = d.companyid where d.origin_dsn = 'ifd' and primarycontact = 1 and d.clientid = 0 and c.origin_dsn = 'ifd' and a.origin_dsn = 'ifd' and c.companyid in (select distinct companyid from products where description IN ('Windows and Doors','Vertical Sliders','Bi-Fold Doors') AND type IN ('wd:f','wd:b') AND material = 'PVCu' AND origin_dsn = 'ifd') )UNION( SELECT DISTINCT c.companyname,c.tradingas,a.quarantined,c.companyid,a.address1,a.city,a.postcode,a.region,c.origin_dsn,d.title,d.firstname,d.surname,d.position,a.telephoneno,a.companyemail,addresstypes,markets FROM companies c join addresses a on c.companyid = a.companyid join contacts d on c.companyid = d.companyid where d.origin_dsn = 'ifd' and primarycontact = 1 and d.clientid = 0 and c.origin_dsn = 'ifd' and a.origin_dsn = 'ifd' and c.companyid in (select distinct companyid from products where type IN ('cr:f','cr:b') AND origin_dsn = 'ifd') )) as t where t.quarantined = 0 and t.origin_dsn = 'ifd' and t.region IN ('Northern Counties','North West','Yorkshire','East Midlands','West Midlands','South West','Home Counties','Southern Counties','Greater London','Scotland','Wales','Northern Ireland') order by companyname
it takes a staggering 3.5 seconds to run in mysql query browser (on my slow, low spec laptop)
This query is made of 2 very similar queries that each take about 1.5 seconds.
My app could require up to 4 similar unions.
Can anyone suggest how it might be written more efficiently using the CI api ?
[eluser]TheFuzzy0ne[/eluser]
Holy poop on a stick! That's quite a query!
So long as you have the right indexes set up on your database and your query is optimised (which I don't think it is), I don't think the query should be quite so slow. You can use [url="http://dev.mysql.com/doc/refman/5.0/en/explain.html"]EXPLAIN[/url] to help you figure out where you're missing indexes, or perhaps break the query up into several smaller queries.
I've reformated your query to make it a little easier to read, but IMHO it's still quite cryptic with the alias' you're using.
Code: SELECT *
FROM (
(
SELECT DISTINCT
c.companyname,
c.tradingas,
a.quarantined,
c.companyid,
a.address1,
a.city,
a.postcode,
a.region,
c.origin_dsn,
d.title,
d.firstname,
d.surname,
d.position,
a.telephoneno,
a.companyemail,
addresstypes,
markets
FROM companies c
JOIN addresses a ON c.companyid = a.companyid
JOIN contacts d ON c.companyid = d.companyid
WHERE d.origin_dsn = ‘ifd’
AND primarycontact = 1
AND d.clientid = 0
AND c.origin_dsn = ‘ifd’
AND a.origin_dsn = ‘ifd’
AND c.companyid IN (
SELECT DISTINCT
companyid
FROM products
WHERE description IN (
‘Windows and Doors’,
‘Vertical Sliders’,
‘Bi-Fold Doors’
)
AND type IN (
‘wd:f’,
‘wd:b’
)
AND material = ‘PVCu’
AND origin_dsn = ‘ifd’
)
)
UNION(
SELECT DISTINCT
c.companyname,
c.tradingas,
a.quarantined,
c.companyid,
a.address1,
a.city,
a.postcode,
a.region,
c.origin_dsn,
d.title,
d.firstname,
d.surname,
d.position,
a.telephoneno,
a.companyemail,
addresstypes,
markets
FROM companies c
JOIN addresses a ON c.companyid = a.companyid
JOIN contacts d ON c.companyid = d.companyid
WHERE d.origin_dsn = ‘ifd’
AND primarycontact = 1
AND d.clientid = 0
AND c.origin_dsn = ‘ifd’
AND a.origin_dsn = ‘ifd’
AND c.companyid IN (
SELECT DISTINCT
companyid
FROM products
WHERE type IN (
‘cr:f’,
‘cr:b’
)
AND origin_dsn = ‘ifd’
)
)
) AS t
WHERE t.quarantined = 0
AND t.origin_dsn = ‘ifd’
AND t.region IN (
‘Northern Counties’,
‘North West’,
‘Yorkshire’,
‘East Midlands’,
‘West Midlands’,
‘South West’,
‘Home Counties’,
‘Southern Counties’,
‘Greater London’,
‘Scotland’,
‘Wales’,
‘Northern Ireland’
)
ORDER BY companyname
Hopefully someone else might be able to give you some input.
|
