In a join, how to prefix all column names with the table it came from

Question

I'm analysing a rather horrible legacy database/codebase, trying to reduce server load by combining queries into joins (including an email alert cron job that typically invokes well over a million separate queries).

SELECT * FROM  class_alerts_holding ah  INNER JOIN class_listings l ON l.id = ah.lid  INNER JOIN class_users u ON u.id = ah.uid LEFT JOIN class_prodimages pi ON pi.pid = ah.lid 

This spits out 120 columns...

aid | id | lid | uid | oid | catName | searchtext | alertfreq | listType | id | owner | title | section | shortDescription | description | featured | price | display | hitcount | dateadded | expiration | url | notified | searchcount | repliedcount | pBold | pHighlighted | notes | ... 

To assist my analysis of how to construct the new queries it would be awesome if I could prefix the columns in the result with the table they came from in the JOIN e.g.

class_alerts_holding.aid | class_alerts_holding.id | class_listings.lid | ... 

Is there a way to achieve this?

Answer 1

You could

select ah.*, l.*, u.*, pi.* from ... 

then the columns will be returned ordered by table at least.

For better distinction between every two sets of columns, you could also add "delimiter" columns like this:

select ah.*, ':', l.*, ':', u.*, ':', pi.* from ... 

^{(Edited to remove explicit aliases as unnecessary, see comments.)}

Answer 2

You could name the fields in your query and give them aliases:

SELECT     ah.whateverfield1 AS 'ah_field1',            ah.whateverfield2 AS 'ah_field2',            l.whateverfield3 AS 'l.field3',            [....] FROM       class_alerts_holding ah  INNER JOIN class_listings l ON l.id = ah.lid  INNER JOIN class_users u ON u.id = ah.uid LEFT JOIN  class_prodimages pi ON pi.pid = ah.lid 

Its a bit of work to manually set up if you have that many fields, but you can simplify this with this query...

SHOW FULL FIELDS FROM your_table_name; 

...and a good text editor and copy & paste.