SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by WorldNews View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.



    Quote Originally Posted by r937 View Post
    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

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 --
    Code:
    ALTER TABLE submit_web_data
    ADD INDEX (category,answer,ix_id)
    then your query would be this --
    Code:
    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'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,027
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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"



    Quote Originally Posted by r937 View Post
    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 --
    Code:
    ALTER TABLE submit_web_data
    ADD INDEX (category,answer,ix_id)
    then your query would be this --
    Code:
    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'

    Anoox search engine volunteer

    www.anoox.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •