Steve Ruby wrote:
>
> 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.
>
Not knowing how HAVING is optimized i've decided for now to go with
one table per question (each of the ANDs) and do a
join (max of 10way) against a temp table for each question...
still doesn't sound like the best route but it will have the
same amount of data in it as if I did one big table and seems
like it would be quicker to update the keys on the smaller
tables
better ideas appreciated.
---------------------------------------------------------------------
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
