1

for simple, I write an example like this

select
a.xx,
a.xx,
...  -- all table a's field

b.date,
b.money

c.date,
c.money,

d.date,
d.money,

e.date,
e.money

from 
a
left join b on a.id = b.a_id
left join c on a.id = c.a_id
left join d on a.id = d.a_id
left join e on a.id = e.a_id

where ... -- some condition
limit 10

In this sql, table b and c is one to one to table a
but table d and e is not that.

With no optimization. table d and e while seem like this

-- ... sql before
left join
(
    select * from
    (select * from d order by date desc) as temp
    group by a_id
) as d on a.id = d.a_id

left join
(
    select * from
    (select * from e order by id desc) as temp
    group by a_id
) as e on a.id = e.a_id
-- ... sql after

But in fact, it is very slow when running this.
table d was a new table, it didn't has larges of data yet(it will be large in future).
table e had million now. subquery of table e make my sql very slow. How to optimization it?

Rick James
  • 135,179
  • 13
  • 127
  • 222
afraid.jpg
  • 965
  • 2
  • 14
  • 31
  • remove those `order by` in your subqueries, its no use. – Ed Bangga Nov 27 '19 at 06:49
  • @ϻᴇᴛᴀʟ ”its no use“ means "it doesn't work" or "it is unnecessary"? if the latter one, l must told that it is necessary, because I only need the latest date in `d` and the newest record in `e` – afraid.jpg Nov 27 '19 at 06:59
  • if you only need the latest record you limit your subquery. `(select * from d order by date desc limit 1)` – Ed Bangga Nov 27 '19 at 07:06
  • maybe I didn't talk clear, I means, I need latest date in every a_id(the some in table `e`), like "a_id - value" : "1 - 1", "1 - 2","1 - 4","2 - 1","2 - 5" I need "1 - 4" and "2 - 5" – afraid.jpg Nov 27 '19 at 07:14
  • if you need the latest date every a_id, it think you are referring to something like this `(select max(date), a_id from d group by a_id)` – Ed Bangga Nov 27 '19 at 07:16
  • @ϻᴇᴛᴀʟ yes, you said right, but to see my example, I need `date`, `money` two field, that's why I did `order by` and then `group by` – afraid.jpg Nov 27 '19 at 07:31
  • Your ''order by group by' trick is a hack that cannot be guaranteed to work. If it was me , I'd see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query and start over – Strawberry Nov 27 '19 at 08:07
  • Nesting like that only slows things down. What was wrong with the original? – Rick James Dec 24 '19 at 07:29
  • Do you have `INDEX(a_id), INDEX(b_id)`, etc? – Rick James Dec 24 '19 at 07:30
  • Sounds like a "groupwise max" problem. – Rick James Dec 24 '19 at 07:33

0 Answers0