>Does anybody know what is the fastest way to run a large multiple
>AND type query on a many-many table.... and the least memory hungry.
Sir, what is a type query, and what in the world is a many-many table?
>For example if I have
>create table test (id int, value int);
>
>which has many-to-many on id, and value
?
>I want to do a select like
>
>select id from test
>where id in (select id from test where value in (1,2,3)
>and id in (select id from test where value in (4,6,7)
>and id in (select id from test where value in (1,6,25)
Even if MySQL supported subqueries, this query would always return
the empty set. I'm really unsure about what you're trying to do, but
I think the following comes close.
SELECT id
FROM test
WHERE value IN (1, 2, 3, 4, 6, 7, 25);
>So I must return users that qualify for at least one value in several
>lists, obviously this is somewhat easy with subselects... Without
>subselects I'm doing this, can anybody tell me of a better way?
>
>create table temp (id int not null, key (id)) type=heap;
>insert into temp select id from test where value in (1,2,3);
>insert into temp select id form test where value in (4,6,7);
>insert inot temp select id from test where value in (1,6,25);
>
>select id from temp, count(*) as numlines from temp
>group by id having numlines = 3;
>
>drop table temp;
>
>there will be anywhere between 1 and 10 of the above lists
>for this query so then I return the qulified useres by doing
>this on the heap table
>
>where the having part is equal to the number of lists that
>were checked.
>
>Obviously the temp table can get very large depending the query,
>currently this seems pretty fast, but I'm concerned that it will
>blow up on me as I get more id values
>
>anybody know a better way? A self join comes to mind but if
>there are many users and say 10 questions then the length
>of the self join result before the WHERE is
>userlines^10 which can be a very big number.
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
