SELECT from results of previous SELECT. Possible?

[morags]

Is it possible to do a SELECT on the database, then run another query on the results of the SELECT?

e.g.

From 100,000 rows I wish to SELECT 500.

From that 500, I want to do 10 SELECTs. I only ask because I assume that it will be a lot quicker to extract rows from the smaller "results" produced by the main SELECT than to run the 10 SELECTs on the main database.

Is this even possible? Or should I just do 10 SELECTs from the main database?

Thanks for any pointers.

[SeanW]

It depends... I'd suggest running EXPLAIN on your query, making sure indices are used properly, and fix any problems. I try not to out think the execution planner, instead I rely on the tools I am give to help it along.

Sean

[ergophobe]

It is possible. You could do an insert select into a temporary table and then select from that, but my guess is that it would all take longer because you would be creating the table, filing it and indexing it on the fly.

It depends a little on how you are doing your query, but I would guess that Sean is right. If you are doing simple joins on indexed columns, this should be really fast with 100K records.

If you create an index that specifically expects the very join that you will be doing, that should make it super super fast for selects.

Look for information on indexes in the MySQL manual