Querying a lot of data from multiple tables using UNION

Hi, I have a requirement where I have to union 5 tables and show records to the user between a certain time limit (Eg : a whole day) When I do a union on all the 5 tables it gives a lot of data and makes it slow (sometimes giving timeout error), so I tried putting a limit of 100 records on every table and using pagination to display 100 records at a time. This is faster but giving issues with datatables searching/pagination/sorting as the results of 5 tables (limiting each to 100) can be max 500 (5 * 100) but we are showing only 100 records every time so retrieval and display wont match.

The other idea I could think of is either by using temp table or a stored proc where the temp table contains the result of all the 5 tables and I put a limit of getting 100 records on the temp table. Haven't tried to implement it yet.

Do you guys have a better option or any ideas on the above.

Do you have any other ideas

jlrdw

4 years ago

Level 75

Probably easier with related data. Just an example, but I had one where the Boss wanted to see accounts payable.

I setup a drill down where he could view one company at a time paginated. Then of course there were more options as well.

The point is to have various searches and ways to view only whats needed. Big joins and unions will always take up more memory. But do make sure you have indexed certain fields.

Edit

Also see @tray2 answer here: https://laracasts.com/discuss/channels/laravel/json-mysql-array

2 likes

Patel

4 years ago

Level 1

@jlrdw Can you elaborate as what do you mean making it easier with related data.

jlrdw

4 years ago

Level 75

If you have ever done accounts receivable you will know:


company a
----listing here of their receivables

company b
----listing here of their receivables

company c
----listing here of their receivables

I usually setup double pagination, so I have a next company link but under company another paginated list of just their receivables.

But also a main search prior to anything with various ways to search.

Boss may want to view company b and only receivables older than 45 days (just example) So think of more efficient techniques to display only needed data. I can't imagine a union on several tables being efficient.

Look at setting up some eloquent related models.

Edit 2

Is that many tables needed, maybe combine some, and try making some relations. Then you can use techniques like eager loading as is done in one to many related data.

Most business queries can be worked out in a one to many. More relations is usually a chaining of one to many.

Also test and see if some join queries would be more efficient, I only use them for monthly reports.

2 likes

Tray2

4 years ago

Level 73

I have a post about using unions in Laravel here https://tray2.se/posts/database-design-part-2

2 likes

sr57

4 years ago

Level 39

@patel

Your question is too general (which db? how many rows? types of fields? index?, which select ? which UNION?, ...)

I should do the following

-1- use UNION ALL

-2- don't use SELECT *

-3- have a look to EXPLAIN

-4- optimize ? (indexes, field types, ...)

-5- do the filters before the UNION,

-6- if you have really a big number of rows, you should probably consider to work with "period tables" that can combine your tables for period of time and be created during night (like logs files)

2 likes

Please or to participate in this conversation.