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.
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)
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
