Using temporary table for zend searches then merging with dataset

Question

Hi all,

In my current setup i have a zend lucene search index which stores the primary keys of my_table rows in the index, along with other unstored fields.

Upon a search the index is queried, the results of which then are looped through and inserted into a mysql temporary table, which is then joined via the primary key onto my_table.

This then allows me to perform advanced sql queries (eg. using the haversine formula, getting data from other joined tables, filtering by score then date and so on).

I just wanted to know whether this was a good implementation. It works, but i'm concerned about the number of inserts as this dataset is likely to be pretty big (few thousand records).

Thanks in advance

Answer 1

Is it necessary to actually insert them into the database? Why don't you just take the list and do something like ...WHERE id IN (id1, id2, id3, ...).