How to feed the list of an inner SELECT to an outer SELECT

[WorldNews]

Hello,

For efficiency purposes, that is less MySQL server querie, I would like to have a SELECT inside a SELECT,
so that the outer SELECT will use the data (list) produced by inner SELECT.

So the inner SELECT is like:
SELECT ix_id FROM anoox.submit_web_data WHERE answer = 'yes' AND category = $category';

THEN the Outer SELECT will be something like this:
SELECT email FROM web_for_indexing WHERE id in (2045058, 2071184);

where (2045058, 2071184) is the list composed of the ids returned by the 1st SELECT.

However, if I just do this:

SELECT email FROM web_for_indexing WHERE id in (SELECT ix_id FROM web_data WHERE answer = 'yes' AND category = '$category');

Then the server comes to screeching halt. So that is not an acceptable solution.

So how can one feed the list generated by the 1st SELECT to the 2nd SELECT that results in proper (speedy) outcome?

Regards,

[r937]

So how can one feed the list generated by the 1st SELECT to the 2nd SELECT that results in proper (speedy) outcome?

pretty much exactly like you wrote

actually, an INNER JOIN would be even better

but neither of them is going to be efficient if you don't have the right indexes

please do a SHOW CREATE TABLE for each table, and i'll show you how to add the indexes

[WorldNews]

Hi,

With all due & respect I do not think "right indexes" is the problem and thus the solution.
But I would not mind being proven wrong. So as per you request:

The show create table for web_for_indexing s:

CREATE TABLE `web_for_indexing` (
`id` int(11) NOT NULL auto_increment,
`url` varchar(250) default NULL,
`email` varchar(250) default NULL,
`submited_date` date default NULL,
`approved_date` date default NULL,
`code` varchar(20) default NULL,
`ip` varchar(15) NOT NULL default '0.0.0.0',
PRIMARY KEY (`id`),
KEY `email_indx` (`email`),
KEY `ip_indx` (`ip`),
KEY `url_indx` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And other show create is:

CREATE TABLE `web_data` (
`id` int(11) NOT NULL auto_increment,
`ix_id` int(11) NOT NULL default '0',
`title` varchar(60) NOT NULL default '',
`descpt` varchar(150) NOT NULL default '',
`category` int(3) NOT NULL default '0',
`date_added` date NOT NULL default '0000-00-00',
`date_updated` date default NULL,
`city` varchar(40) default NULL,
`country` varchar(40) default NULL,
`kw1` varchar(25) default NULL,
`kw2` varchar(25) default NULL,
`kw3` varchar(25) default NULL,
`kw4` varchar(25) default NULL,
`user_id` int(11) default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


FYI, when I run the 2 SELECT commands that I listed in original message by themselves, the result is instantaneous.
However if I put the 1st SELECT inside the 2nd one as I had written, the server comes to its knees, which obviously
means there is something totally wrong with that scheme. So again love to see how your suggestion will make the
combined SELECTs instantaneous as when they are run individually.

Regards.

pretty much exactly like you wrote
actually, an INNER JOIN would be even better

but neither of them is going to be efficient if you don't have the right indexes

please do a SHOW CREATE TABLE for each table, and i'll show you how to add the indexes

[r937]

something doesn't add up here

your inner query uses a column called "answer" in the table called "submit_web_data"

but the SHOW CREATE TABLE you posted was for a table called "web_data" and it's lacking that column

you changed your table already, perhaps?

anyhow, let's assume your tables are as you first posted them

run this --

ALTER TABLE submit_web_data
ADD INDEX (category,answer,ix_id)

then your query would be this --

SELECT web_for_indexing.email 
  FROM submit_web_data
INNER
  JOIN web_for_indexing 
    ON web_for_indexing.id = submit_web_data.ix_id
 WHERE submit_web_data.category = $category
   AND submit_web_data.answer = 'yes'

[WorldNews]

Hi,

Your suggestion works well. That is now list of emails comes back in an instant.

I must say you are the God of "Inner Join"

something doesn't add up here
your inner query uses a column called "answer" in the table called "submit_web_data"

but the SHOW CREATE TABLE you posted was for a table called "web_data" and it's lacking that column

you changed your table already, perhaps?

anyhow, let's assume your tables are as you first posted them

run this --

ALTER TABLE submit_web_data
ADD INDEX (category,answer,ix_id)

then your query would be this --

SELECT web_for_indexing.email 
  FROM submit_web_data
INNER
  JOIN web_for_indexing 
    ON web_for_indexing.id = submit_web_data.ix_id
 WHERE submit_web_data.category = $category
   AND submit_web_data.answer = 'yes'