MySQL/PHP - Join two database tables w/ identical rows - Databases forum at WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

MySQL/PHP - Join two database tables w/ identical rows

brokaddr

5:26 pm on Mar 17, 2012 (gmt 0)

A script I'm working on has a set of two database tables, both with identical rows; but the row content is different.

This script as-is already has about 2 joins on each query; would it be feasible to join these two tables or simply make separate pages for each?

I'm not sure if I'm using the terminology correctly, but in a nutshell; in phpmyadmin:
Under database1:
table1
table2
table3
table4

table1 & table2 have only id as the similar row; joins on (t1.id=t2.id)

table3 & table4 have identical structure to table1 & table2, but there is no way to join like t1 & t2, because the id row only matches like so:
(t3.id=t4.id)

So, is it feasible to have the data from these tables in the same query, or better to have separate pages for t1/t2 & t3/t4?

brokaddr

2:09 am on Mar 18, 2012 (gmt 0)

I was able to achieve the desired effect by UNION SELECT and giving one of the tables a prefix:
SELECT FROM t1.table1...

But it's rather slow - any tips to speed up the pace? There's well under a million rows, which I gather is small compared to some databases out there.

For a hasty fix, instead of using the slow UNION SELECT, if mysql_num_rows() == 0{ //switch table names } seems to be working quite well; I am curious if this is the most efficient way to go about handling these types of queries?

Dijkgraaf

10:54 pm on Mar 18, 2012 (gmt 0)

If you are using UNION and you aren't expecting contents to be duplicated across the two tables use UNION ALL, this will speed up the query.