Create a view for A table with new columns JOIN(ed) on B table queries about the last maching records with certain parameters. How to simplify code? I'm not sure if I can ask this question here. Let me know if I can't. Basically, my code works fine, but I would like to improve the code itself.

I'd like to improve this code to require fewer lines (faster is optional, but not mandatory). I think my code could be optimized in space because it performs two queries about the same table in the two join statements.

Here is the dbfiddle of my query. Note that even when the page said "MySQL 8.0" I'm using MySQL 5.7 (an old version where a lot of new stuff can't be done).

First, we create the (simplified) tables:

CREATE TABLE products (
 code varchar(10) NOT NULL,
 name varchar(100) NOT NULL,
 PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE transactions (
 date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 code varchar(10) NOT NULL,
 mode enum('Buy','Sell','Count','Return') NOT NULL,
 stock int(11) NOT NULL,
 average_price decimal(14,4) NOT NULL,
 PRIMARY KEY (date),
 KEY Code (code),
 CONSTRAINT transactions_ibfk_1 FOREIGN KEY (code) REFERENCES products (code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Then we populate with records:

INSERT INTO products(code, name) VALUES
  ("A", "Alpha"),
  ("B", "Beta")
INSERT INTO transactions(date, code, mode, stock, average_price) VALUES
  ("2000-1-2", "A", "Buy", 5, 1),
  ("2000-1-3", "B", "Buy", 4, 2),
  ("2000-1-4", "B", "Sell", 2, 3),
  ("2000-1-5", "B", "Sell", 1, 4),
  ("2000-1-6", "A", "Buy", 6, 5),
  ("2000-1-7", "A", "Buy", 8, 6)


Finally, we make the view (if you prefer in your answer you can alter the table instead of making a view, but I didn't figure how):

Create VIEW products_view_joinA_and_JoinB
  AS select t1.code, t1.name, t2.stock, t2.average_price as cost_usd, t5.date as last_buy
    from (products t1 left join (
      select t3.stock, t3.code, t3.average_price from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code) order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code))
      left join(
        select t6.code, t6.date from transactions t6
        where (t6.date = (
          select t7.date from transactions t7 where (t7.code = t6.code and t7.mode = "Buy") order by t7.date desc limit 1)
       )) t5 on((t5.code = t1.code)))


Finally, we test it:


SELECT code, name, stock, cost_usd, last_buy FROM products_view_joinA_and_JoinB



code | name  | stock | cost_usd | last_buy           
:--- | :---- | ----: | -------: | :------------------
B    | Beta  |     1 |   4.0000 | 2000-01-03 00:00:00
A    | Alpha |     8 |   6.0000 | 2000-01-07 00:00:00



For a better understanding I make these two views with half code each one:




Create VIEW products_view_joinA
  AS select t1.code, t1.name, t2.stock, t2.average_price as cost_usd
    from (products t1 left join (
      select t3.stock, t3.code, t3.average_price from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code) order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code)))






SELECT code, name, stock, cost_usd FROM products_view_joinA



code | name  | stock | cost_usd
:--- | :---- | ----: | -------:
B    | Beta  |     1 |   4.0000
A    | Alpha |     8 |   6.0000



And




Create VIEW products_view_joinB
  AS select t1.code, t1.name, t2.date as last_buy
    from (products t1 left join (
      select t3.code, t3.date from transactions t3
      where (t3.date = (
        select t4.date from transactions t4 where (t4.code = t3.code and t4.mode = "Buy") order by t4.date desc limit 1)
      )) t2 on((t2.code = t1.code)))






SELECT code, name, last_buy FROM products_view_joinB



code | name  | last_buy           
:--- | :---- | :------------------
A    | Alpha | 2000-01-07 00:00:00
B    | Beta  | 2000-01-03 00:00:00



The output works fine, but the code itself has some duplicated lines that I would like to remove, I think that would also increase its speed.
(Getting rid of some redundant parens, plus wrapping and indenting:)

Create  VIEW products_view_joinA_and_JoinB AS 
select  t1.code, t1.name, t2.stock, t2.average_price as cost_usd,
        t5.date as last_buy
    from  (products t1
    left join  ( SELECT  t3.stock, t3.code, t3.average_price
                    from  transactions t3
                    where  t3.date = 
                          ( SELECT  t4.date
                                from  transactions t4
                                where  t4.code = t3.code
                                order by  t4.date desc
                                limit  1
                          )
               ) t2  on t2.code = t1.code
    left join  ( SELECT  t6.code, t6.date
                    from  transactions t6
                    where  t6.date = 
                          ( SELECT  t7.date
                                from  transactions t7
                                where  t7.code = t6.code
                                  and  t7.mode = "Buy" 
                                order by  t7.date desc
                                limit  1
                          )
               ) t5  on t5.code = t1.code
          )


transactions needs an index on (code, date), but you effectively have such since InnoDB tacks the PK on the end of any secondary key.

Other indexes needed:

transactions:  INDEX(code, mode, -- in either order
                     date)  -- (for t7)


If you are using an old version of MySQL, LEFT JOIN ( SELECT ... ) is problematic since it would repeatedly execute the derived table (that subquery).  5.6 improves on it.  Ditto for having two JOIN ( SELECT ... ) in a query.

Do you need LEFT?

VIEWs are syntactic sugar, and rarely (perhaps never) provide any performance.  Sometimes, they make performance worse.  So, experiment with the query outside a VIEW, then see if it still works as well in a VIEW.

If you are using MySQL 8.0 (or MariaDB 10.2), you could consider CTEs and use WITH to introduce the common code.

Other approaches to redundant code:

SELECT @var := ... FROM ...;  -- to compute a single value that is needed repeatedly.


or...

CREATE TEMPORARY TABLE t  SELECT ...;  -- to build a table that you need to use twice.