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

Databases
1

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,

2

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

3

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.

4

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'
5

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” :slight_smile: