Mysql – 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

join;MySQLview

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.

Best Answer

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

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

MySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause in Views

OBSERVATION #1

The MySQL Documentation gives you the Restrictions on Views

It is not possible to create an index on a view.

Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Subqueries cannot be used in the FROM clause of a view.

Therefore

The result set of a View would be some temp table used for row-by-row retrieval
There is no existing mechanism to index the result set coming from a view

Creating indexes only help the underlying base tables within the JOIN a the View, not the View itself.

OBSERVATION #2

You mentioned this

Some suggest to do aggregation in subquery before join which is not possible as MySQL doesn't allow subquery in FROM clause.

That's not true. I have a post in StackOverflow (Fetching a Single Row from Join Table) that clearly demonstrates doing an aggregation in a subquery followed by all kinds of JOINs to that subquery.