Yes, I know, I've been a bad boy.
I have nested several views, and now it takes eight seconds to process
the result set from this query:
select * from products where product = '1456'

Any ideas of a better way to accomplish this?

Here's an index of the views:
psub
pmain_sub
pmain
products

And the views themselves:

CREATE VIEW psub AS
SELECT CONCAT(pmainT.product, woodtype.short) AS SKU,
psubT.ID,
psubT.price,
psubT.pmain_ID,
psubT.woodtype_ID,
psubT.weight,
psubT.oldweight,
psubT.turning
FROM woodtype
INNER JOIN psubT
ON woodtype.ID = psubT.woodtype_ID
INNER JOIN pmainT
ON pmainT.ID = psubT.pmain_ID
WHERE psubT.active = 1
UNION ALL
SELECT CONCAT(pmainT.product, '.5', woodtype.short) AS SKU,
psubT.ID + .5 AS Expr1,
ROUND(psubT.price * .75, 2) AS Expr2,
psubT.pmain_ID + .5 AS Expr3,
psubT.woodtype_ID,
psubT.weight * .5 AS Expr4,
psubT.oldweight,
psubT.turning
FROM woodtype
INNER JOIN psubT
ON woodtype.ID = psubT.woodtype_ID
INNER JOIN pmainT
ON pmainT.ID = psubT.pmain_ID
WHERE (pmainT.milling = 1)
AND psubT.active = 1
UNION ALL
SELECT CONCAT(pmainT.product , '.6', woodtype.short ) AS SKU,
psubT.ID + .6 AS id,
ROUND(psubT.price * .875, 2) AS price,
psubT.pmain_ID + .6 AS Expr1,
psubT.woodtype_ID,
psubT.weight * .5 AS weight,
psubT.oldweight,
psubT.turning
FROM woodtype
INNER JOIN psubT
ON woodtype.ID = psubT.woodtype_ID
INNER JOIN pmainT
ON pmainT.ID = psubT.pmain_ID
WHERE (pmainT.milling = 1)
AND psubT.active = 1
UNION ALL
SELECT CONCAT(pmainT.product , '.75' , woodtype.short ) AS SKU,
psubT.ID + .75 AS id,
ROUND(psubT.price * 1.5, 2) AS price,
psubT.pmain_ID + .75 AS Expr1,
psubT.woodtype_ID,
psubT.weight * .75 AS weight,
psubT.oldweight,
psubT.turning
FROM woodtype
INNER JOIN psubT
ON woodtype.ID = psubT.woodtype_ID
INNER JOIN pmainT
ON pmainT.ID = psubT.pmain_ID
WHERE (pmainT.milling = 1)
AND (pmainT.twosquares = 1)
AND psubT.active = 1
UNION ALL
SELECT CONCAT(pmainT.product , '.75' , woodtype.short ) AS SKU,
psubT.ID + .75 AS id,
ROUND(psubT.price * 1.65, 2) AS price,
psubT.pmain_ID + .75 AS Expr1,
psubT.woodtype_ID,
psubT.weight * .75 AS weight,
psubT.oldweight,
psubT.turning
FROM woodtype
INNER JOIN psubT
ON woodtype.ID = psubT.woodtype_ID
INNER JOIN pmainT
ON pmainT.ID = psubT.pmain_ID
WHERE (pmainT.milling = 1)
AND (pmainT.twosquares = 0)
AND psubT.active = 1
UNION ALL
SELECT CONCAT(pmainT.product , '.5' , woodtype.short) AS SKU,
psubT.ID + .5 AS Expr1,
ROUND(psubT.price * .9, 2) AS Expr2,
psubT.pmain_ID + .5 AS Expr3,
psubT.woodtype_ID,
psubT.weight * .5 AS Expr4,
psubT.oldweight,
psubT.turning
FROM woodtype
INNER JOIN psubT
ON woodtype.ID = psubT.woodtype_ID
INNER JOIN pmainT
ON pmainT.ID = psubT.pmain_ID
WHERE (pmainT.milling = 2)
AND psubT.active = 1

drop view pmain_sub;
CREATE VIEW pmain_sub AS
SELECT 1 AS original,
ID,
type_ID,
dimensions,
leg_name,
product,
leg_description,
milling,
oversize,
trackproduction,
active,
height,
SORT,
customize,
comingsoon,
twosquares
FROM pmainT
WHERE (active = 1)
UNION ALL
SELECT 0 AS original,
ID + .5 AS Expr1,
type_ID,
dimensions,
leg_name + ' Rectangular Half Leg' AS Expr2,
product + '.5' AS product,
leg_description,
milling,
oversize,
trackproduction,
active,
height,
SORT,
customize ,
comingsoon,
twosquares
FROM pmainT AS pmainT_4
WHERE (milling = 1)
AND (active = 1)
UNION ALL
SELECT 0 AS original,
ID + .75 AS id,
type_ID,
dimensions,
leg_name + ' Three Quarter Leg' AS Expr1,
product + '.75' AS product,
leg_description,
milling,
oversize,
trackproduction,
active,
height,
SORT,
customize ,
comingsoon,
twosquares
FROM pmainT AS pmainT_3
WHERE (milling = 1)
AND (active = 1)
UNION ALL
SELECT 0 AS original,
ID + .6 AS id,
type_ID,
dimensions,
leg_name + ' Diagonal Half Leg' AS Expr1,
product + '.6' AS product,
leg_description,
milling,
oversize,
trackproduction,
active,
height,
SORT,
customize ,
comingsoon,
twosquares
FROM pmainT AS pmainT_2
WHERE (milling = 1)
AND (active = 1)
UNION ALL
SELECT 0 AS original,
ID + .5 AS Expr1,
type_ID,
dimensions,
leg_name + ' Half Leg' AS Expr2,
product + '.5' AS product,
leg_description,
milling,
oversize,
trackproduction,
active,
height,
SORT,
customize ,
comingsoon,
twosquares
FROM pmainT AS pmainT_1
WHERE (milling = 2)
AND (active = 1);

drop view pmain;
CREATE VIEW pmain AS
SELECT original,
ID,
type_ID,
dimensions,
leg_name,
product,
leg_description,
milling,
oversize,
trackproduction,
active,
height,
SORT,
customize,
comingsoon,
twosquares
FROM
pmain_sub
WHERE (active = 1);

drop view products;
CREATE VIEW products AS

SELECT psub.ID,
psub.SKU,
psub.price,
psub.weight,
pmain.dimensions,
type.SOFTCARTCATEGORY,
woodtype.wood,
pmain.leg_name,
pmain.product,
pmain.oversize,
pmain.original,
pmain.milling,
pmain.ID AS pmain_id,
woodtype.short,
pmain.trackproduction,
psub.turning,
pmain.leg_description,
pmain.sort AS pmain_sort,
pmain.customize,
pmain.comingsoon,
pmain.twosquares
FROM psub
LEFT OUTER JOIN woodtype
ON psub.woodtype_ID = woodtype.ID
LEFT OUTER JOIN pmain
LEFT OUTER JOIN type
ON pmain.type_ID = type.ID
ON psub.pmain_ID = pmain.ID

On Tue, 12 Jun 2007 18:36:13 -0000, Matthew256 <PRESENT321@gmail.com>
wrote:

>Yes, I know, I've been a bad boy.
>I have nested several views, and now it takes eight seconds to process
>the result set from this query:
>select * from products where product = '1456'
>
>Any ideas of a better way to accomplish this?
>
>Here's an index of the views:
>psub
>pmain_sub
>pmain
>products
>
>And the views themselves:

Miam miam... spaghettis !

On Jun 13, 12:12 am, subtenante <zzsubtenant...@gmail.com> wrote:
> On Tue, 12 Jun 2007 18:36:13 -0000, Matthew256 <PRESENT...@gmail.com>
> wrote:
>
> >Yes, I know, I've been a bad boy.
> >I have nested several views, and now it takes eight seconds to process
> >the result set from this query:
> >select * from products where product = '1456'
>
> >Any ideas of a better way to accomplish this?
>
> >Here's an index of the views:
> >psub
> >pmain_sub
> >pmain
> >products
>
> >And the views themselves:
>
> Miam miam... spaghettis !

Yup, you're right.
I just rewrote the whole thing.
I think I just needed to hear a fellow developer's shocked response to
motivate me.
You have preformed this service quite well