I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so:
CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT NULL, `custacct` varchar(21) DEFAULT NULL, UNIQUE KEY `pend_inv` (`invoice`), KEY `pend_acct` (`custacct`), KEY `pend_pid` (`pid`) ... ) ENGINE=InnoDB CREATE TABLE `done` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT NULL, `custacct` varchar(21) DEFAULT NULL, UNIQUE KEY `done_inv` (`invoice`), KEY `done_acct` (`custacct`), KEY `done_pid` (`pid`) ... ) ENGINE=InnoDB
And two similar tables, customers and locations:
CREATE TABLE `customers` ( `acct` varchar(14) NOT NULL, ... PRIMARY KEY (`acct`), ... ) ENGINE=InnoDB
So, yes, when an invoice is not yet fulfilled, it's in the "pending" table, then when the company's finished with it, it gets moved to the "done" table. So any given invoice will only be on one of the two tables.
Unfortunately, we're still actively using the proprietary software that runs on this schema, so I am powerless to fix it. The "pending" table will usually have around 9000 rows, whereas the "done" table is ~800,000 and counting.
For two years (the full extent of my LAMP/MySQL experience), I have been writing UNIONs to capture information from the customers or locations tables, with the first query joining customers/location/both against "pending" and the second joining customers/locations/both against "done."
I've recently had a nagging feeling that writing two nearly-identical queries UNIONed together is probably newbish and should be done differently. So I finally applied my more recent learning to a problem I had "solved" in my first few weeks of working with MySQL and realized that I can just use a derived table with a UNION inside that subquery to achieve the same results with less typing.
The execution time is near-similar, usually 0.01s for the derived query and slightly less than that for the UNIONed query, but the dervied table query uses far fewer characters and requires less time to type out.
So my question for those with more experience, is using a derived table subquery more sane and maintainable in the long run? It's easier for me, the programmer, so that makes me suspicious that maybe it's not the best practice!
Sample queries below:
select c.whatever,l.whatever from customers c join (select d.custacct as acct,d.lid from done d where d.invoice=123456 union select p.custacct,p.lid from pending p where p.invoice=123456 ) as combi on c.acct=combi.acct join locations l on combi.lid=l.lid; select c.whatever,l.whatever from done d join customers c on d.custacct=c.acct join locations l on d.lid=l.lid where d.invoice=123456 union select c2.whatever,l2.whatever from pending p join customers c2 on p.custacct=c2.acct join locations l2 on p.lid=l2.lid where p.invoice=123456;