Before you think I’ve posted an animal story in my MySQL category please read on. For reference, The RAT and the CAT is something I wrote back in 2006, that explains the CAT part.
I was reviewing the website performance tonight of a client. I had access to an idle system so being lazy in collecting SQL after my initial audit of the system and MySQL, I turned on the General Query log and hit the home page. NOTE: Do not do this on a running production system, especially with any volume.
What I got was 77 lines of output (after I removed newlines in the queries). 74 were queries, and 72 were from the same table, via some unnecessarily complex sub-joins. I wish I could have laughed but it wasn’t really funny.
My Friday wisdom for writing SQL is this:
- Don’t write duplicate SQL statements
- Look the entire process/function, not just an individual SQL statement
- Don’t use SELECT *. While I could go into detail, in this example they were 4, I repeat 4 TEXT columns where output was not used.
- All developers should know what a QEP is, and use it.
- The best performance improvement you can make with an SQL statement, is to never execute it. Even better, never think about writing it.
I trust just looking at this below will impact more wisdom.
1 Connect XXXX 1 Init DB XXXX 1 Query SELECT * FROM categories where cparent_id = 0 AND show_on_navigation =1 order by cid ASC 1 Query Select home_title, home_keyword from general_meta where id = '1' 1 Query SELECT * FROM categories where cparent_id = 0 and show_on_navigation = 1 order by cid ASC 1 Query SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '45' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '2') AS pretext FROM categories cp WHERE cp.cparent_id = '2' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '5') AS pretext FROM categories cp WHERE cp.cparent_id = '5' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '6') AS pretext FROM categories cp WHERE cp.cparent_id = '6' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM categories where cparent_id = '81' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '82' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '83' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '84' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '85' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '86' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '87' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '8') AS pretext FROM categories cp WHERE cp.cparent_id = '8' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM categories where cparent_id = '133' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '134' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '135' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '9') AS pretext FROM categories cp WHERE cp.cparent_id = '9' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM categories where cparent_id = '88' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '40') AS pretext FROM categories cp WHERE cp.cparent_id = '40' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM categories where cparent_id = '669' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '670' and show_on_navigation = 1 order by cid ASC 1 Query SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '46' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '10') AS pretext FROM categories cp WHERE cp.cparent_id = '10' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM categories where cparent_id = '90' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '91' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '92' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '93' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '94' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '95' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '96' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '97' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '11') AS pretext FROM categories cp WHERE cp.cparent_id = '11' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '12') AS pretext FROM categories cp WHERE cp.cparent_id = '12' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '13') AS pretext FROM categories cp WHERE cp.cparent_id = '13' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '14') AS pretext FROM categories cp WHERE cp.cparent_id = '14' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '69') AS pretext FROM categories cp WHERE cp.cparent_id = '69' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '47' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '15') AS pretext FROM categories cp WHERE cp.cparent_id = '15' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM categories where cparent_id = '98' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '99' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '100' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '101' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '102' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '103' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM categories where cparent_id = '104' and show_on_navigation = 1 order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '16') AS pretext FROM categories cp WHERE cp.cparent_id = '16' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '17') AS pretext FROM categories cp WHERE cp.cparent_id = '17' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '18') AS pretext FROM categories cp WHERE cp.cparent_id = '18' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '19') AS pretext FROM categories cp WHERE cp.cparent_id = '19' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '20') AS pretext FROM categories cp WHERE cp.cparent_id = '20' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '45' order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '2') AS pretext FROM categories cp WHERE cp.cparent_id = '2' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '5') AS pretext FROM categories cp WHERE cp.cparent_id = '5' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '6') AS pretext FROM categories cp WHERE cp.cparent_id = '6' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '8') AS pretext FROM categories cp WHERE cp.cparent_id = '8' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '9') AS pretext FROM categories cp WHERE cp.cparent_id = '9' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '40') AS pretext FROM categories cp WHERE cp.cparent_id = '40' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '46' order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '10') AS pretext FROM categories cp WHERE cp.cparent_id = '10' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '11') AS pretext FROM categories cp WHERE cp.cparent_id = '11' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '12') AS pretext FROM categories cp WHERE cp.cparent_id = '12' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '13') AS pretext FROM categories cp WHERE cp.cparent_id = '13' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '14') AS pretext FROM categories cp WHERE cp.cparent_id = '14' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '69') AS pretext FROM categories cp WHERE cp.cparent_id = '69' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT *, CASE WHEN cname = 'Babies Clothes' THEN 'baby clothes' ELSE (CASE WHEN cname = 'Babies Toys' THEN 'baby toys' ELSE (CASE WHEN cname = 'Babies Shoes' THEN 'baby shoes' ELSE cname END) END) END AS ccname FROM categories where cparent_id = '47' order by cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '15') AS pretext FROM categories cp WHERE cp.cparent_id = '15' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '16') AS pretext FROM categories cp WHERE cp.cparent_id = '16' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '17') AS pretext FROM categories cp WHERE cp.cparent_id = '17' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '18') AS pretext FROM categories cp WHERE cp.cparent_id = '18' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '19') AS pretext FROM categories cp WHERE cp.cparent_id = '19' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query SELECT * FROM ( SELECT * , ( SELECT CASE WHEN cname LIKE '%Babies Clothes%' THEN 'baby ' ELSE ( CASE WHEN cname = 'Men''s Clothes' THEN 'mens ' ELSE ( CASE WHEN cname = 'Women''s Clothes' THEN 'womens ' ELSE ( CASE WHEN cname LIKE '%Boy''s Clothes%' THEN 'boys ' ELSE ( CASE WHEN cname LIKE '%Girl''s Clothes%' THEN 'girls ' ELSE '' END ) END ) END ) END ) END AS pretext FROM categories WHERE cid = '20') AS pretext FROM categories cp WHERE cp.cparent_id = '20' AND show_on_navigation =1)abc ORDER BY cid ASC 1 Query select * from general_meta where id=1 1 Quit
Toby says
Wow. This isn’t SQL, it’s WTF.
Wayne says
At least the subselects use simple index searches :p
It’s stuff like this that makes me think there really is a market out there for website performance consulting, of and that some programmers need to be taken out to a nice quiet dessert somewhere and abandoned.
Tim Little says
I know, you realize some of the things that are wrong with the snippet… and I know you realize that because you posted it, but I thought that I’d point out a few tiny things :
You are doing a SELECT * FROM categories where cparent_id = 0 early on (like the first query) so it’s treating the cparent_id as a number (integer). But later, all subsequent references to that column are of string or textual types. That’s likely to bugger with the indexes.
Either way, it seems that you’d really be best served by having the web-application have an internal resource of those categories (since they seem limited in number, and you’re never JOINing on them). It’s probably an internally self-rerential construct showing who/what is related to whom/what by id. You can read that once (from the database) at the beginning of the web application into some internal resource.
The nested SELECT *’s and against a column with LIKE and with prefixed and suffixed wild-cards is astonishing, and made all the more impressive with the CASE / ELSE and more CASEs. Can you use a FULLTEXT index? Is the table MyISAM?
Sorry though, I’ve seen way worse. But it is impressive that all but one SELECT uses the * and that there are 77 per page.
Anonymous says
During the the first week at my current employer, the database servers were coming to a halt and would result in downtime measured in hours. After doing some auditing of the application, using a similar technique that you described (start simple first!), I found that the majority of the pages were executing way too many queries. One I fondly remember, executed 2700+ queries to simply list 100 top ranked items. The problem was exactly as you described of not looking at the overall goal, and only the granular components.