Ordering results by the IN statement...

[dalecosp]

So, I have an array of ID's and will grab several items of information from the DB for each ID. These ID's are arranged in order of relevance from a fulltext query in the constructor of the search class (or from a cached file on a RAMdisk if the search is cached). So, the query without an ORDER BY clause, looks like this:

select id,title,some_var
from products 
where id in 
($list_of_ids_from_FTS);

There are, apparently, two ways to perform the query and return the result set:

1.

select id,title,some_var
from products 
where id in 
($list_of_ids_from_FTS)
order by find_in_set
($list_of_ids_from_FTS);

2. Use a temporary table.

Have you done this both ways? Is a temp table faster? I'm thinking #1 is easier, but I would prefer the search to render quickly even if I have to read a chapter or two ;-)

[Derokorian]

Having done this exact thing before, I would use option 1. I've found any time I can avoid a temporary table its worth it, but in this particular case its not a feeling but a piece of knowledge from experience! Granted I didn't notice a difference until either the list of ids became long (around 25+ but that may differ depending on hardware).

[dalecosp]

Thank you. I'm having lists of a few hundred up to about 10K ids. So far my tests have been inconclusive. When you got > 25, what did you observe?