MySQL – Using LIMIT Within JOIN

join;limitsMySQLsubquery

I have an 1:m association.

symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created.
I need to join latest symbols with company_key_statistics, but I need only latest company_key_statistics.
For example I need to get ORCL and MSFT symbols and only their latest company_key_statistics.

So far I have tried this.

SELECT `symbols`.`id`, 
       `symbols`.`symbol`, 
       `statistics`.
       `marketCapitalization` 
FROM   `symbols` 
       LEFT JOIN (SELECT `s`.`companyId`, 
                         `s`.`marketCapitalization` 
                  FROM   `company_key_statistics` AS `s`
                  WHERE  `s`.`companyId` = `symbols`.`id` 
                  ORDER by `createdAt` 
                  DESC LIMIT 1) AS `statistics` 
ON     `symbols`.`id` = `statistics`.`companyId` 
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

But unfortunately I found that I can't use columns from parent query in JOIN subqueries.

How can I achieve to this?

Best Answer

Try this:

SELECT sym.id, sym.symbol, s.marketCapitalization 
  FROM symbols AS sym 
  INNER JOIN company_key_statistics AS s 
     ON sym.id = s.companyId 
  INNER JOIN (SELECT companyId, MAX(createdAt) AS createdAt 
                FROM company_key_statistics 
                GROUP BY companyId) AS smax 
     ON smax.companyId = s.companyId AND smax.createdAt = s.createdAt 
  WHERE sym.symbol IN ('ORCL','SNAP');

Since your posted example was a LEFT JOIN, maybe you want also companies without market capitalization data in your answer. In this case, we have to nest the subselects, in this way:

SELECT sym.id, sym.symbol, s.marketCapitalization 
  FROM symbols AS sym 
  LEFT JOIN (SELECT * 
               FROM company_key_statistics
               INNER JOIN (SELECT companyId, MAX(createdAt) AS createdAt 
                             FROM company_key_statistics 
                             GROUP BY companyId) AS smax 
               USING (companyId, createdAt)) AS s 
    ON sym.id = s.companyId 
 WHERE sym.symbol IN ('ORCL','SNAP');

The general problem we are addressing here is called groupwise maximum and many SO and DBA.SE answers deal with it. Check this one for instance.

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 – Slow performance of MySQL Join Query

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE 
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

Mysql – Slow performance of MySQL Join Query

Related Question