Is it better to use a JOIN or two separate queries?

[tina onak]

Hi

I have a table called mem with the following structure:

CREATE TABLE IF NOT EXISTS `mem` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`vals` text COLLATE utf8_bin NOT NULL,
`height` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `height` (`height`),
FULLTEXT KEY `values` (`vals`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=10001 ;

And I have a table called options2:

CREATE TABLE IF NOT EXISTS `options2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL,
`attributeid` int(10) unsigned NOT NULL,
`value` int(11) NOT NULL,
`txt` varchar(1000) NOT NULL,
PRIMARY KEY (`id`),
KEY `value` (`value`),
KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=500001 ;

The table mem contains 10.000 rows of data and the table options2 contains approx. 400.000 rows of data. The two tables are related by mem.id and options2.userid .

*****************
I need to display the data from both tables on one of my php pages. I could do that with two different approaches:

APPROACH 1

On my php page I execute two different sql queries, one for the mem table and one for the options2 table:

SELECT vals, height FROM mem WHERE id = '8366';
SELECT userid, attributeid, value, txt FROM options2 WHERE userid = '8366';
.. code here for displaying the data from the tables ....

APPROACH 2

On my php page I execute one sql query by joining the two tables together:

SELECT options2.id, options2.userid, options2.attributeid, options2.value, options2.txt, mem.id, mem.vals, mem.height
FROM options2
JOIN mem
ON (options2.userid = mem.id)
WHERE options2.userid = '8366';
.. code here for displaying the data from the tables ....

This query would give me the data as following:

id userid attributeid value txt vals height
58557 8366 2 2 dsdf 73 96
58558 8366 3 3 erdf 73 96
58559 8366 4 4 fdtz 73 96
58561 8366 6 6 gfgf 73 96
58562 8366 7 7 etrt 73 96
...and so on.. the query would return on average about 500 rows of data..

As you can see the data in the 'vals' and 'height' columns is repeated for each row. These two columns are from the joined mem table.

Considering the fact that when using APPROACH 2 the data in the 'vals' and 'height' columns is repeated, is it faster to use APPROACH 1 or APPROACH 2?