Professional Web Applications Themes

Untangling nested views - MySQL

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) ...

  1. #1

    Default Untangling nested views

    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

    Matthew256 Guest

  2. #2

    Default Re: Untangling nested views

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

    Miam miam... spaghettis !
    subtenante Guest

  3. #3

    Default Re: Untangling nested views

    On Jun 13, 12:12 am, subtenante <com> wrote: 



    >
    > Miam miam... spaghettis ![/ref]

    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 :)

    Matthew256 Guest

Similar Threads

  1. views in 8.0
    By Jason Tesser in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 11th, 01:26 PM
  2. 2 views
    By duff@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 4
    Last Post: May 28th, 06:03 PM
  3. Views or WHERE
    By Ray in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 18th, 12:38 PM
  4. Views
    By Guy Brown in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 08:47 PM
  5. Replies: 4
    Last Post: July 8th, 07:00 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