select a.id,b.name from a
left join b on a.lead_id = b.lead_id
where a.status = 'Created' and date(a.createdenter code here_at) = '2018-11-19'
-
It is strange to see this query. Is there anything special you wanted to achieve apart from just outer-joining table2 rows to table1 rows? I think fa06 is right in their answer that all you are actually doing is just a simple outer join. (There is a copy and paste error in your query, though. You call it table1, but access the table with the name lead.) Next time please tell us about the tables involved, especially what the unique keys are. That can make a difference. And it's always helpful to show sample table data and result, too.Thorsten Kettner– Thorsten Kettner2018-12-21 06:10:42 +00:00Commented Dec 21, 2018 at 6:10
-
Above comment refers to the query originally posted. The query shown now is the one suggested in fa06's answer. @Narendra Rawat: Don't change your requests so drastically. And please don't ignore comments. I told you that you should provide more information on the tables and show sample data. You changed your request completely without even reacting to this.Thorsten Kettner– Thorsten Kettner2018-12-21 13:16:24 +00:00Commented Dec 21, 2018 at 13:16
-
For SQL optimization requests you should: 1) describe the tables, 2) list all existing indexes, 3) show the execution plan (dev.mysql.com/doc/refman/8.0/en/using-explain.html). (But for now see my comment on fa06's answer. I think this query is as good as it gets. All you have to do is create appropriate indexes.)Thorsten Kettner– Thorsten Kettner2018-12-21 13:19:37 +00:00Commented Dec 21, 2018 at 13:19
Add a comment
|
2 Answers
You can try below -
select a.lead_id, b.abc from table1 a
left join table2 b on a.lead_id = b.lead_id
where a.status = 'Created' and date(a.created_at) = '2018-11-19'
4 Comments
Thorsten Kettner
Same query I came up with. Took me a while to understand that the original query does just this in spite of all those subqueries and in clause.
Narendra Rawat
both the table a and b has large dataset
Narendra Rawat
select a.lead_id, b.abc from table1 a left join table2 b on a.lead_id = b.lead_id where a.status = 'Created' and date(a.created_at) = '2018-11-19'
Thorsten Kettner
@Narendra Rawat: This is the straight-forward query. Don't try to meddle with it in order to "optimize" it somehow. Provide appropriate indexes instead. My suggestion:
create index idxa on a(status, created_at, lead_id) and create index idxb on b(lead_id, abc). These are both covering indexes granting quick access on the desired a records first and efficient joins with b then.Can you break the queries in two separate single queries and then merge the result-sets based on their condition you used in application side? I think this will at least give better performance than the existing one if the scenario comes for multiple users performing this operation. I mean like this:
query-1:
select a.id from table1 a where a.status = 'Created' and date(a.createdenter code here_at) = '2018-11-19'
And query-2:
select b.lead_id, b.name from table2 b.
Then merge the two resultsets. Or using a.id list(from resultset) from query-1 result using IN clause for query-2 i.e:
select b.lead_id, b.name from table2 b where b.lead_id IN( all your a.ids here)
[must check for empty list if there is no a.id at all].
