0

I have the query shown bellow:

    SELECT i2016.rid,
           NOW(),
           NOW(),
           1,
           1,
           1,
           0,
           'changeme',
           '',
           '',
           '',
           '',
           i2014.total,
           i2015.total,
           i2016.total,
           '',
           i2016.categoryid, 
           i2016.accid
      FROM invreport i2016
 LEFT JOIN invreport i2015
        ON i2016.rid = i2015.rid
 LEFT JOIN invreport i2014
        ON i2016.rid = i2014.rid    
     WHERE i2016.period = '2016' 
       AND i2015.period = '2015'
       AND i2014.period = '2014'
  GROUP BY i2016.`rid`

It takes over an hour for 1000 rows if not longer. I need help to speed it up as I have over million rows stored in the line table.

It runs from the following VIEW:

 SELECT YEAR(`invoice_lines`.`date_entered`) AS `period`,
        SUM(`invoice_lines`.`quantity`)      AS `total`,
        `invoice_lines`.`pasteldb2`          AS `pasteldb2`,
        `invoice_lines`.`categoryid`         AS `categoryid`,
        `invoice_lines`.`accid`              AS `accid`,    
        CONCAT(`invoice_lines`.`pasteldb2`, 
               `invoice_lines`.`accid`, 
               `invoice_lines`.`categoryid`) AS `rid`
    FROM `invoice_lines`
   WHERE (`invoice_lines`.`id` LIKE 'nem%')
GROUP BY `invoice_lines`.`pasteldb2`,
         YEAR(`invoice_lines`.`date_entered`),
         `invoice_lines`.`accid`,
         `invoice_lines`.`categoryid`
2
  • 1
    please post create table and explain plan for the query as well Nov 30 '16 at 11:39
  • 1
    Paul van Zyl - Please elobarate your question for understandable. Nov 30 '16 at 12:24
0

VIEWs are handly. But they are not necessarily efficient.

In this case, you might be able to make it run 3 times as fast by not calling the VIEW 3 times. Instead, since the SELECT in the VIEW is already doing the GROUP BY year, take advantage of it.

Essentially the view gives you 3 rows, but you want to turn it into 3 columns. Before continuing with my answer, please confirm that. That is, do SELECT * FROM invoice_lines and see if you get all the 'right' info, but laid out 'wrong'.

After that we can talk about "pivoting" the 3 rows into 3 columns. You can get hints of doing that by searching this forum.

If you need to discuss further, provide SHOW CREATE TABLE invoice_lines,

Or, am I confused? Do you have one table per year? Bad.

0

pack all into select over views and single SQL query - work good only when performance not top priority, when You not worry about execution time, have over powerful hardware and code elegance have more priority than performance.

Unfortunately it not work in all cases and there are many strategies for achieve final goal - report over 3 year in this case.

try to avoid use function in WHERE and GROUP BY operations, such as:

YEAR(`invoice_lines`.`date_entered`)

hidden call of function:

i2016.period = '2016'
i2016.rid= i2015.rid - also really CONCAT over all rows

direction for optimisation:

  • add column Year and populate it by any methods - manually, from application (script), by trigger
  • add index to this new column
  • or test 5.7 with calculated columns + index over calculated column

next could be (with steps from above): create not the View, but MySQL analog of "materialised view" - temp table with indexes over rid and year.

Pivoting could be solution, but again it could be not fasted way

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.