Other comments:
s_product_sales_log has no PRIMARY KEY. All InnoDB tables 'should' have an
explicit PK.
INT(5) is not what you think. INT is always a 32-bit, 4-byte quantity,
regardless of the number.
Use TINYINT UNSIGNED, SMALLINT UNSIGNED, etc. wherever reasonable.
> KEY `is_value_added` (`is_value_added`),
A single-column INDEX on a flag, ENUM, and other low-cardinality field, is
almost always useless.
Slightly more readable:
SELECT pc AS `Profit Centre`, CONCAT('$', FORMAT(prp,
2)) AS Paid, CONCAT('$', FORMAT(prt, 2)) AS Trade,
CONCAT('$', FORMAT(prtot, 2)) AS `Both`
FROM
( SELECT tt.`Profit Centre` AS pc, mt.Paid / tt.Hours AS prp,
mt.Trade / tt.Hours AS prt, mt.tot / tt.Hours AS prtot
FROM
( SELECT SUM(TIMESTAMPDIFF(MINUTE, tl.`in`, tl.`out`)/60) AS
Hours,
pc.ID AS pcid, pc.name AS `Profit Centre`
FROM s_timelog tl
LEFT JOIN s_project proj ON tl.project_id = proj.id
LEFT JOIN s_profit_centre pc ON proj.profit_centre = pc.ID
WHERE proj.private = 'no'
AND YEAR(tl.`in`) = '{{{1}}}'
GROUP BY `Profit Centre`) tt
LEFT JOIN
( SELECT SUM(case when sales.Type NOT IN('barter', 'work
trade') then `Total` else 0 end) AS Paid,
SUM(case when sales.Type IN('barter', 'work trade')
then `Total` else 0 end) AS Trade,
SUM(`Total`) AS tot, pc.ID AS pcid, pc.name AS `Profit
Centre`
FROM s_product_sales_log sales
LEFT JOIN s_product prod ON sales.Product = prod.ID
LEFT JOIN s_profit_centre pc ON prod.profit_centre = pc.ID
WHERE YEAR(sales.`Date`) = '{{{1}}}'
GROUP BY `Profit Centre`
) mt ON mt.pcid = tt.pcid
WHERE mt.tot != 0
) xx
Performance issues...
> WHERE YEAR(sales.`Date`) = '{{{1}}}'
won't use
> KEY `Date` (`Date`),
because the column (Date) is hidden in a function. A workaround:
WHERE `Date` >= '{{{1}}}-01-01'
AND `Date` < '{{{1}}}-01-01' + INTERVAL 1 YEAR
JOINing two subqueries -- There is no way to index either of them, so the JOIN
will have to do a table scan of one temp table for every row of the other temp
table.
(The alternative is to CREATE TEMPORARY TABLE... with an index, for one of the
subqueries.)
It would probably be better to move the mt.tot!=0 test inside:
GROUP BY `Profit Centre`
) mt ON mt.pcid = tt.pcid
WHERE mt.tot != 0
) xx
-->
GROUP BY `Profit Centre`
HAVING tot != 0 -- added
) mt ON mt.pcid = tt.pcid
-- removed: WHERE mt.tot != 0
) xx
That would make mt have fewer rows, hence that unindexed JOIN could run faster.
> -----Original Message-----
> From: Jan Steinman [mailto:j...@bytesmiths.com]
> Sent: Tuesday, September 18, 2012 9:26 PM
> To: Rick James
> Cc: mysql@lists.mysql.com
> Subject: Re: Making myself crazy...
>
> Thanks for your help, Rick.
>
> The solution that seems to be working was to have both the FROM and
> LEFT JOIN be grouped subqueries. I realize this is probably not the
> best way of doing things, but it seems fast enough at present.
>
> Here is the query, followed by the table definitions, resulting in the
> following data:
> http://www.EcoReality.org/wiki/2012_gross_productivity
>
> -- (The following query is from a template, in which "{{{1}}}" is
> replaced by a year, such as "2012".) SELECT
> pc AS `Profit Centre`,
> CONCAT('$', FORMAT(prp, 2)) AS Paid,
> CONCAT('$', FORMAT(prt, 2)) AS Trade,
> CONCAT('$', FORMAT(prtot, 2)) AS `Both` FROM (SELECT
> tt.`Profit Centre` AS pc,
> mt.Paid / tt.Hours AS prp,
> mt.Trade / tt.Hours AS prt,
> mt.tot / tt.Hours AS prtot
> FROM
> (SELECT
> SUM(TIMESTAMPDIFF(MINUTE, tl.`in`, tl.`out`)/60) AS Hours,
> pc.ID AS pcid,
> pc.name AS `Profit Centre`
> FROM s_timelog tl
> LEFT JOIN
> s_project proj ON tl.project_id = proj.id LEFT JOIN
> s_profit_centre pc ON proj.profit_centre = pc.ID WHERE proj.private =
> 'no' AND YEAR(tl.`in`) = '{{{1}}}'
> GROUP BY `Profit Centre`) tt
> LEFT JOIN
> (SELECT
> SUM(case when sales.Type NOT IN('barter', 'work trade') then
> `Total` else 0 end) AS Paid,
> SUM(case when sales.Type IN('barter', 'work trade') then `Total`
> else 0 end) AS Trade,
> SUM(`Total`) AS tot,
> pc.ID AS pcid,
> pc.name AS `Profit Centre`
> FROM s_product_sales_log sales
> LEFT JOIN
> s_product prod ON sales.Product = prod.ID
> LEFT JOIN
> s_profit_centre pc ON prod.profit_centre = pc.ID
> WHERE YEAR(sales.`Date`) = '{{{1}}}'
> GROUP BY `Profit Centre`) mt
> ON mt.pcid = tt.pcid
> WHERE mt.tot != 0) xx
>
> --"s_timelog" and "s_product_sales_log" are the two "ends" of the
> query, each related by an intermediate table to the "centre" of the
> query, "s_profit_centre":
> -- s_timelog.project_id --> s_project.id, s_project.profit_centre -->
> s_profit_centre.ID
> -- s_product_sales_log.Product --> s_product.ID,
> s_product.profit_centre --> s_profit_centre.ID
>
> ----------------
>
> -- 18,739 rows, growing by ~100 records monthly CREATE TABLE
> `s_timelog` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `mw_user_id` int(11) unsigned NOT NULL default '2',
> `in` datetime NOT NULL,
> `out` datetime NOT NULL,
> `project_id` int(11) unsigned NOT NULL,
> `activity_id` int(11) unsigned NOT NULL,
> `accomplishment` varchar(255) character set utf8 NOT NULL,
> `Paid` enum('yes','no') character set utf8 NOT NULL default 'no'
> COMMENT 'hours that are compensated',
> `Class_B_qualified` enum('yes','no') character set utf8 NOT NULL
> default 'no',
> `updated` timestamp NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> PRIMARY KEY (`id`),
> KEY `mw_user_id` (`mw_user_id`),
> KEY `in` (`in`),
> KEY `project_id` (`project_id`),
> KEY `activity_id` (`activity_id`),
> KEY `out` (`out`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
> COMMENT='EcoReality time log.' AUTO_INCREMENT=19462 ;
> --
> -- RELATIONS FOR TABLE `s_timelog`:
> -- `user_id`
> -- `mw_user` -> `user_id`
> -- `mw_user_id`
> -- `mw_user` -> `user_id`
> -- `project_id`
> -- `s_project` -> `id`
> -- `activity_id`
> -- `s_activity` -> `id`
>
> ----------------
>
> -- 143 rows, growing by one or two a month CREATE TABLE IF NOT EXISTS
> `s_project` (
> `id` int(6) unsigned NOT NULL auto_increment,
> `profit_centre` int(10) unsigned NOT NULL default '0',
> `profit_centre_amortized` int(3) unsigned default '0' COMMENT 'for
> capital projects',
> `name` varchar(62) NOT NULL,
> `URL` varchar(255) default NULL,
> `need` varchar(255) NOT NULL,
> `description` text NOT NULL,
> `super_id` int(11) unsigned NOT NULL default '0',
> `type` enum('capital','operations') NOT NULL default 'operations',
> `duration` enum('fixed','ongoing') NOT NULL default 'fixed',
> `status` enum('abandoned','agreed','cancelled','completed','on
> hold','ongoing','requested') NOT NULL default 'requested',
> `start` date NOT NULL,
> `end` date NOT NULL,
> `completed` date NOT NULL,
> `depends_on_id` int(6) unsigned NOT NULL default '0',
> `budget_expense` decimal(7,2) NOT NULL,
> `budget_effort` decimal(7,2) NOT NULL,
> `effort_units` enum('minutes','hours','days','weeks','months') NOT
> NULL default 'hours',
> `steward_id` int(5) unsigned NOT NULL default '0',
> `private` enum('yes','no') NOT NULL default 'no',
> `updated` timestamp NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> `notes` text NOT NULL,
> PRIMARY KEY (`id`),
> UNIQUE KEY `name` (`name`),
> KEY `duration` (`duration`),
> KEY `status` (`status`),
> KEY `super_id` (`super_id`,`depends_on_id`,`steward_id`),
> KEY `depends_on_id` (`depends_on_id`,`steward_id`),
> KEY `steward_id` (`steward_id`),
> KEY `profit_centre` (`profit_centre`),
> KEY `profit_centre_amortized` (`profit_centre_amortized`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='EcoReality (and other)
> projects that require resources.' AUTO_INCREMENT=145 ;
>
> --
> -- RELATIONS FOR TABLE `s_project`:
> -- `steward`
> -- `mw_user` -> `user_id`
> -- `super_id`
> -- `s_project` -> `id`
> -- `depends_on_id`
> -- `s_project` -> `id`
> -- `steward_id`
> -- `mw_user` -> `user_id`
> -- `profit_centre`
> -- `s_profit_centre` -> `ID`
> -- `profit_centre_amortized`
> -- `s_profit_centre` -> `ID`
>
> ----------------
>
> -- 12 records, growing by one or two a year CREATE TABLE IF NOT EXISTS
> `s_profit_centre` (
> `ID` int(3) unsigned NOT NULL,
> `name` varchar(255) NOT NULL,
> `description` text NOT NULL,
> `steward` int(5) unsigned NOT NULL,
> `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
> `notes` text NOT NULL,
> PRIMARY KEY (`ID`),
> KEY `steward` (`steward`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> --
> -- RELATIONS FOR TABLE `s_profit_centre`:
> -- `steward`
> -- `mw_user` -> `user_id`
>
>
> ----------------
>
> -- 3,090 rows, growing by a few hundred monthly CREATE TABLE
> `s_product_sales_log` (
> `Venue` enum('EcoReality grounds','farm gate','delivered','Tuesday
> market','roadside','Saturday market','store','subscription','Yellow
> House kitchen') character set utf8 NOT NULL default 'farm gate',
> `Seller` int(11) unsigned NOT NULL default '212',
> `Who` int(11) unsigned NOT NULL default '0',
> `Date` date NOT NULL,
> `Quantity` decimal(8,3) NOT NULL,
> `Unit`
> enum('bale','box','bunch','dozen','each','grams','kilograms','liters','
> ounces','pints','pounds','quarts') character set utf8 collate utf8_bin
> NOT NULL default 'each',
> `Total` decimal(8,2) NOT NULL,
> `Type` enum('barter','cash','check','invoice','PayPal','work trade')
> character set utf8 NOT NULL default 'cash',
> `Product` int(11) unsigned NOT NULL,
> KEY `Product` (`Product`),
> KEY `Date` (`Date`),
> KEY `Seller` (`Seller`),
> KEY `Who_2` (`Who`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> --
> -- RELATIONS FOR TABLE `s_product_sales_log`:
> -- `Seller`
> -- `mw_user` -> `user_id`
> -- `Who`
> -- `mw_user` -> `user_id`
> -- `Product`
> -- `s_product` -> `ID`
>
> ----------------
>
> -- 186 records, growing by ~1 monthly
> CREATE TABLE IF NOT EXISTS `s_product` (
> `ID` int(10) unsigned NOT NULL auto_increment,
> `super` int(11) unsigned default NULL COMMENT 'generalization',
> `name` varchar(31) character set utf8 NOT NULL,
> `units`
> enum('kilograms','grams','pounds','ounces','liters','each','cords','bal
> es') character set utf8 NOT NULL default 'kilograms' COMMENT 'preferred
> unit',
> `profit_centre` int(3) unsigned NOT NULL default '3',
> `tax_qualified` enum('yes','no') character set utf8 NOT NULL default
> 'yes',
> `tax_livestock_born` enum('yes','no') character set utf8 NOT NULL
> default 'no',
> `tax_poultry_egg` enum('yes','no') character set utf8 NOT NULL
> default 'no',
> `is_value_added` enum('yes','no') character set utf8 NOT NULL default
> 'no',
> `plant_ID` int(5) unsigned default NULL,
> `description` varchar(255) character set utf8 NOT NULL,
> PRIMARY KEY (`ID`),
> UNIQUE KEY `Name` (`name`),
> KEY `Description` (`description`),
> KEY `is_value_added` (`is_value_added`),
> KEY `profit_centre` (`profit_centre`),
> KEY `super` (`super`),
> KEY `plant_ID` (`plant_ID`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of
> EcoReality farm products' AUTO_INCREMENT=186 ;
>
> --
> -- RELATIONS FOR TABLE `s_product`:
> -- `super`
> -- `s_product` -> `ID`
> -- `profit_centre`
> -- `s_profit_centre` -> `ID`
> -- `plant_ID`
> -- `s_plants` -> `ID`
>
>
> On 17 Sep 12, at 16:12, Rick James wrote:
>
> > If the subquery-version is not too slow, live with it.
> >
> > If necessary, make your non-grouped SELECT a subquery and apply GROUP
> BY outside. Thus:
> >
> > SELECT ... FROM ( SELECT non-grouped... ) GROUP BY ...
> >
> > Could you provide that; we might be able to simplify it.
> > Also provide SHOW CREATE TABLE for each table.
> > How many rows in each table? (approx)
> >
> >> -----Original Message-----
> >> From: Jan Steinman [mailto:j...@bytesmiths.com]
> >> Sent: Sunday, September 16, 2012 3:45 PM
> >> To: mysql@lists.mysql.com
> >> Subject: Making myself crazy...
> >>
> >> I'm having trouble figuring out how to make a query. It seems
> simple,
> >> but it's making me crazy right now. Please point out where my
> >> thinking is addled...
> >>
> >> I have the following (simplified) table structure:
> >>
> >> s.timelog --> s.projects --> s.profit_centres
> >>
> >> s.product.sales --> s.products --> s.profit_centres
> >>
> >> (The arrows refer to many-to-one relationships: each Timelog record
> >> refers to a single Project, which is in a single Profit Centre. Each
> >> record has an opaque ID referred to by records in the table to its
> >> left.)
> >>
> >> What I want to do is figure out productivity: sales per hour worked
> >> per Profit Centre.
> >>
> >> I can do this at a gross level -- without grouping by Profit Centres
> >> -- with a subquery: simply sum up the Lales and divide by the sum of
> >> the labour (Timelog.out - Timelog.in). But I suspect even this can
> be
> >> done without a subquery.
> >>
> >> But needing to do two levels of indirection has stymied me: how can
> I
> >> group $/hour by Profit Centre?
> >>
> >> You can see the SQL here if you wish:
> >> http://www.ecoreality.org/wiki/Template:Annual_gross_productivity
> >> _for
> >>
> >> Then click on the "Source" link to see the code. (You won't be able
> >> to change it without logging in.)
> >>
> >> I'm using CASE to pivot tables to separate out monetary versus
> >> barter/trade income.
> >>
> >> Thanks for any help offered!
> >>
> >> ----------------
> >> :::: Respond with love and compassion, rather than punishment and
> >> retaliation, and an angry person will be disarmed. Trade the need to
> >> be right for a loving relationship. It is a trade you - and everyone
> >> around you - will benefit from. -- Dean Van Leuven
> >> :::: Jan Steinman, EcoReality Co-op ::::
> >>
> >>
> >>
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql
> >
>
> ----------------
> :::: She can't sing in more languages than most people can't speak in.
> -- JWS
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
