Timeline for Query "all of" across many-to-many relation
Current License: CC BY-SA 4.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 23, 2019 at 18:00 | comment | added | Vérace |
I've added a method which includes the set operator INTERSECT - may be OK performance wise for you with decent indexes?
|
|
| Apr 23, 2019 at 18:00 | history | edited | Vérace | CC BY-SA 4.0 |
added 600 characters in body
|
| Apr 23, 2019 at 17:51 | comment | added | Eldamir | The given tables are simply a contrived example, but for my actual db, each table has index on primary and foreign keys | |
| Apr 23, 2019 at 17:49 | comment | added | Vérace | How are your indexes on your tables? | |
| Apr 23, 2019 at 17:47 | history | edited | Vérace | CC BY-SA 4.0 |
added 2 characters in body
|
| Apr 23, 2019 at 17:46 | comment | added | Eldamir | I can get behind that statement, for sure, but as it stands, your proposed solution simply isn't feasible for my usage. One of my worst datasets took 8s to finish, and with the accepted solution, it is at 0.2s. Any other suggestions you can provide is much appreciated :) | |
| Apr 23, 2019 at 17:44 | comment | added | Vérace | Yes, but you'll always have that scaling issue, be your data in arrays, JSON, XML... whatever. You are, IMHO, far far better off keeping your data in a relational framework and using plain old vanilla SQL with proper indexes. I would only go to arrays, JSON, XML, Key-Value... whatever, if I had thoroughly exhausted all the possibilities available with SQL - which increase year on year - CTEs, Window functions...! Scaling problems will occur with any solution! | |
| Apr 23, 2019 at 17:40 | comment | added | Eldamir | You're absolutely right, but you'll find the same solution in my question, along with a note that it doesn't scale well, if the list of groups become big. What if I want to check on 10, 50, 100 groups? That will be a tonne if joins and a performance degradation. | |
| Apr 23, 2019 at 17:30 | history | answered | Vérace | CC BY-SA 4.0 |