Timeline for Query with HAVING clause is taking too long
Current License: CC BY-SA 4.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 18, 2019 at 16:58 | comment | added | Rick James |
@shivajibhosale - Then you don't need COUNT. EXISTS is a better option. LEFT JOIN would also work, but would give 1 or NULL. COALESCE() can turn NULL into 0.
|
|
| Feb 18, 2019 at 7:26 | comment | added | shivaji bhosale | isLiked is kind of true or false it returns 0 or 1. | |
| Feb 17, 2019 at 20:40 | comment | added | Rick James |
@shivajibhosale - Before I tackle things, let's discuss isLiked. Is that effectively a true/false value? Or can isLiked be >= 2?
|
|
| Feb 17, 2019 at 20:26 | comment | added | shivaji bhosale | Yes I changed to 4G. Update: Exist clause not giving expected output in every situation so I reverted it. So now its taking 2seconds to execute the query, and if I remove order by clause it is taking few milliseconds. I am updating the question so please check. | |
| Feb 17, 2019 at 17:56 | comment | added | Rick James | @shivajibhosale - If there are no other applications on that server, make the buffer_pool 4G when the data gets too big to fit in the 1G. | |
| Feb 17, 2019 at 17:54 | vote | accept | shivaji bhosale | ||
| Feb 17, 2019 at 17:54 | comment | added | shivaji bhosale | Thank you so much. By replacing having with where and using EXIST () solved my problem, Now it's taking less than 1 second. Regarding "foo LIKE '%' and created_at > '0'" those data will come dynamically from the server according to request so I placed conditions. Data is something 50k+ rows in every table which increases daily. RAM is 8gb of server and innodb_buffer_pool_size is 1G | |
| Feb 16, 2019 at 17:31 | history | answered | Rick James | CC BY-SA 4.0 |