mysql query from returned result

[cmd]

hey there,

i run a select query in mysql and returns thousands of rows. i want to do a query out of returned result set, and a query out of that result set, and so on so on.

i think i can do this two ways:
1. i was thinking of creating a temporary table and inserting the results into this table, but if a column is fulltext its gotta index the temporary table and that takes a long time.

2. do a select from a select query

anyone with previous experience making search engines can give me some input on the better method, or any other methods?

-Chris

[lunatic]

If you are careful and can get your logic down right (or have query analyzer) then a nested select can be very powerful.

:hr:

[Digitalosophy]

Well can't you just set a variable to store the results and then work from there? Not sure exactly what you want to do, but to create a new table for temp searches seems a bit too much to me.

[cmd]

hey there,

lunatic: just found out nested queries can only be done in verions 4.1 or higher. i'm using 4.0.20 so i was pulling my hair out for nothing!

digitalosophy: the results being returned contain thousands of rows and storing them in vars i don't think would be a good idea. i don't wanna overload the server and bog it down when many people are searching...

more details to what i want:
i have images with keywords, the user enters keywords and returns a result set. the user has an option to search within this result set. i was thinking of doing it this way:

just add the new text words into the query, for example the current query is:
SELECT * FROM images WHERE MATCH (keywords) AGAINST ('+Planets +Earth' IN BOOLEAN MODE);

so this will look for Planets and Earth in the keywords. if the user enters another keyword i'll just append the new keyword like so...
SELECT * FROM images WHERE MATCH (keywords) AGAINST ('+Planets +Earth +Keyword' IN BOOLEAN MODE);


-Chris