I have 3 tables. One is a collections table that gets searched. It
contains collections of posts from another table. Fulltext index on
the subject. Second one is a lot bigger table that has the posts.
Then I have a third table that has each collection id matched with the
post id. each collection id could have anywhere from 1-500 posts
connected to it. In the posts table there is 2 columns with numbers.
I would like to do a fulltext search on the collections table and get
the results from it but also get the sum of the 2 number columns. If
I do 2 queries and use the ids from the collections and do a join with
the third table and the posts table it is very fast. Thing is people
search the collections and get like 500 results at a time. and to do
500 extra queries does not work. Here is a sample of the 2 queries.

SELECT `id`,`subject`,`numfiles`,`groups`,`size`,`from`,` date`,`nfo`
FROM temp.collections WHERE match (subject) against ('$find' in
boolean mode) order by `date` desc

SELECT sum(num),sum(`max`) FROM mainsubjects, collectionpartids where
cid = $cid and fid = id

On 21 May, 06:15, Extremest <a...@binindex.net> wrote:
> I have 3 tables. One is a collections table that gets searched. It
> contains collections of posts from another table. Fulltext index on
> the subject. Second one is a lot bigger table that has the posts.
> Then I have a third table that has each collection id matched with the
> post id. each collection id could have anywhere from 1-500 posts
> connected to it. In the posts table there is 2 columns with numbers.
> I would like to do a fulltext search on the collections table and get
> the results from it but also get the sum of the 2 number columns. If
> I do 2 queries and use the ids from the collections and do a join with
> the third table and the posts table it is very fast. Thing is people
> search the collections and get like 500 results at a time. and to do
> 500 extra queries does not work. Here is a sample of the 2 queries.
>
> SELECT `id`,`subject`,`numfiles`,`groups`,`size`,`from`,` date`,`nfo`
> FROM temp.collections WHERE match (subject) against ('$find' in
> boolean mode) order by `date` desc
>
> SELECT sum(num),sum(`max`) FROM mainsubjects, collectionpartids where
> cid = $cid and fid = id

You have not constructed these queries in such a way that anyone can
tell what field is in what table and the table names in the queries do
not match those in the explanation.

Please make it clearer what field in what table relates to what field
in another table and which table is which.

Then, we'll have a go at helping.

ok see if this is better. The cid is for collections id and the fid
is for mainsubjects id.

SELECT
`collections`.`id`,`collections`.`subject`,`collec tions`.`numfiles`,`collections`.`groups`,`collecti ons`.`size`,`collections`.`from`,`collections`.`da te`,`collections`.`nfo`
FROM temp.collections WHERE match (`collections`.`subject`) against
('$find' in
boolean mode) order by `collections`.`date` desc

SELECT sum(mainsubjects.num),sum(mainsubjects.`max`) FROM
mainsubjects, collectionpartids where
collectionpartids.cid = $cid and collectionpartids.fid =
mainsubjects.id

I've come up with this query but it is very different on times. some
are done in like a sec or less and some take like 50 secs.

SELECT
`collections`.`id`,`collections`.`subject`,`collec tions`.`numfiles`,`collections`.`groups`,`collecti ons`.`size`,`collections`.`from`,`collections`.`da te`,`collections`.`nfo`,sum(mainsubjects.num),sum( mainsubjects.`max`)
FROM temp.collections, temp.mainsubjects, collectionpartids WHERE
match (`collections`.`subject`) against ('dvdr' in
boolean mode) and collections.id = collectionpartids.cid and
collectionpartids.fid = mainsubjects.id group by collections.id order
by `collections`.`date` desc