Join two table is slow so how to we should do it?

eldynn

Hi everyone!

We have this table that is related to another like this:

• table 1 (20k records) → column fk_jobs[] (any array of id of table 2)
• table 2 (10k records) → column id (an id)

When we do a query all paginated the speed is very good (~0.07s), but if we want to join the table to use a search index in a fuzzy search that is in the second table we want (I think) to join the tables.

When we do so the request is so slow, like not ending (more than 360s, I didn't wait in the debugger to see if it does return something). To do it since it's an array we do a join like:

• table2.id in table1.fk_jobs

Are we doing something wrong?

P.S. : on another note it seem the request is not ending (no timeout) when you close the debugger and the CPU usage goes to 100% for a while.

Ray Deck

Joins work well for indexed lookups. They do not work well for fuzzy searches. To support a more performant search I would try putting the data you want the search to go over into a single table. In the medium term, you'll find that fuzzy search is limited, and for more power you can consider a dedicated search engine system like Algolia.

We work on the complex end of database interactions as part of our focus on the hardest 5% during our daily office hours on State Change Pro.

MattLaro

Perhaps I'm oldschool (and anyone can join in and tell me to shut up haha), but I usually advise people against using arrays in a database unless it's for a pure informational standpoint that does not contribute to database operations (ex: raw json, info that will be read by the backend, a list of words you'll do stats on, etc.)… and even then I'd say let the back end work with arrays and your database work with rows, but that's just me being oldschool 😅.

If it's linked to anything operational (ex: foreign keys), check your design needs and if it's a many to many relation, make an intermediate table where each row links a row of table 1 to a row of table 2. You'll find your queries a lot easier to work with!

Foreign keys shouldn't be something that hinders performance at all.

eldynn

So my issue mostly come from the fact that you cannot index an array you think?