Which is better: a single query or first determine the order, then select details with an IN list?-CodePudding

We have a Postgres database with tables as shown below where ITEM tells the type and ordering/ranking:

ITEM  (id, itemId, type, ranking)

TEXTBOOK (id, title, total_pages)
TEXTBOOK_DATA (id, price, availability, supplier)

MAGAZINE (id, title, edition)
MAGAZINE_DATA (id, price, supplier)

When we display on UI we need to display the list like below:

TxtbookTitle1, total_pages, price, availability, supplier
MagazineTitle1, edition, price, supplier
TxtbookTitle2, total_pages, price, availability, supplier
MagazineTitle2, edition, price, supplier

There will be a lot of records to filter so we are considering whether Option1 is better than Option2 (ie. faster, less computing-intensive), or if there is another better Option without radically changing the tables. Help appreciated. Thanks.

Option #1: Left join the tables, then pass Where clause to filter

SELECT 
    Item.id, 
    COALESCE(Txtbook.title, Magazine.title) "title",    
    COALESCE(TxtbookData.price, MagazineData.price) "price",
    COALESCE(TxtbookData.supplier, MagazineData.supplier) "supplier", 
    Txtbook.total_pages, Magazine.edition, TxtbookData.availability
FROM 
    ITEM Item
LEFT JOIN 
    TEXTBOOK Txtbook ON Item.id = Txtbook.id -- TextBook
LEFT JOIN 
    TEXTBOOK_DATA TxtbookData ON Item.id = TxtbookData.id
LEFT JOIN 
    MAGAZINE Magazine ON Item.id = Magazine.id -- Magazine
LEFT JOIN 
    MAGAZINE_DATA MagazineData ON Item.id = MagazineData.id
WHERE 
    Item.itemId IN (1, 2, 3, 4)
ORDER BY 
    Item.ranking

Option #2: Run first query to get types and ordering/ranking then use programming language (Java/Javascript/C#) to filter Ids. Then run a separate query for each type

SELECT Item.id, Item.type
FROM ITEM Item
WHERE Item.itemId  in (1, 2, 3, 4)
ORDER BY Item.ranking

SELECT Txtbook.id, Txtbook.title, TxtbookData.price, TxtbookData.supplier, Txtbook.total_pages, Magazine.edition, TxtbookData.availability
FROM TEXTBOOK Txtbook
INNER JOIN TEXTBOOK_DATA TxtbookData on Txtbook.id = TxtbookData.id
WHERE Txtbook.id in (%filteredIds%)

SELECT Magazine.id, Magazine.title, MagazineData.price, MagazineData.supplier, Magazine.edition
FROM MAGAZINE Magazine
INNER JOIN MAGAZINE_DATA MagazineData on Magazine.id = MagazineData.id
WHERE Magazine.id in (%filteredIds%)

CodePudding user response：

Option 1 is always better. Your option 2 is a bad habit:

You have to run three SQL statements instead of one
Those long IN lists are cumbersome to create and process, and they can make SQL statements very long

Moreover, there is no point in sorting the results of the first query, since that ordering will be lost in the other queries, which require another ORDER BY. Finally, you'd have to merge the two ordered lists.