Timeline for Getting stuck with creating better index (Optimize Join)
Current License: CC BY-SA 3.0
17 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 7, 2020 at 22:23 | history | edited | CommunityBot |
added [query-performance] to 2412 questions - Shog9 (Id=1924)
|
|
| Jan 22, 2018 at 22:53 | answer | added | Rick James | timeline score: 1 | |
| Jan 22, 2018 at 18:06 | history | edited | TheDataGuy | CC BY-SA 3.0 |
added 8 characters in body
|
| Jan 22, 2018 at 17:42 | comment | added | ypercubeᵀᴹ | Let us continue this discussion in chat. | |
| Jan 22, 2018 at 17:38 | comment | added | ypercubeᵀᴹ |
Did you try the query by editing the LEFT JOIN tbl3 T3 ON T3.id = T2.id to INNER JOIN tbl3 T3 ON T3.id = T1.id?
|
|
| Jan 22, 2018 at 17:34 | comment | added | ypercubeᵀᴹ | You can at least post definitions that work. The ones you have will produce errors if someone tries to run an example. You can use dbfiddle.uk or sqlfiddle.com or rextester.com | |
| Jan 22, 2018 at 17:29 | comment | added | TheDataGuy |
Sorry, I can't post the complete definition. Added the index T3 (frnd_id, id), but still its taking 5s. :(
|
|
| Jan 22, 2018 at 17:24 | comment | added | ypercubeᵀᴹ |
Do you have an index on T3 (frnd_id, id)? Try adding one and also rewrite the query to join T3 with: JOIN tbl3 T3 ON T3.id = T1.id
|
|
| Jan 22, 2018 at 17:22 | comment | added | ypercubeᵀᴹ | OK. But you should really put the whole table definitions in the question. Including all columns and foreign keys. | |
| Jan 22, 2018 at 17:20 | comment | added | TheDataGuy |
Oh, wrong table,I have updated now, T1 id is the FK of T2
|
|
| Jan 22, 2018 at 17:20 | history | edited | TheDataGuy | CC BY-SA 3.0 |
deleted 14 characters in body
|
| Jan 22, 2018 at 17:18 | comment | added | ypercubeᵀᴹ |
Probably irrelevant to the performance issue but T1 LEFT JOIN T2 ON T1.id = T2.id? And both id being auto_increment? What is going on there?
|
|
| Jan 22, 2018 at 17:14 | comment | added | TheDataGuy | Question updated. | |
| Jan 22, 2018 at 17:14 | history | edited | TheDataGuy | CC BY-SA 3.0 |
added 1580 characters in body
|
| Jan 22, 2018 at 17:00 | comment | added | TheDataGuy | This is the exact query. I'll post my table definition. | |
| Jan 22, 2018 at 16:58 | comment | added | Jonathan Fite | Could you please post the entire table definition as well as the current execution plan? Also, is this a truly representative query or do the number of arguments in the where clause change? | |
| Jan 22, 2018 at 16:41 | history | asked | TheDataGuy | CC BY-SA 3.0 |