dynamic searchable fields, best practice?

Posted by boblu on Stack Overflow See other posts from Stack Overflow or by boblu
Published on 2010-05-12T09:02:21Z Indexed on 2010/05/12 9:04 UTC
Read the original article Hit count: 237

Filed under:
|
|

I have a Lexicon model, and I want user to be able to create dynamic feature to every lexicon.

And I have a complicate search interface that let user search on every single feature (including the dynamic ones) belonged to Lexicon model.

I could have used a serialized text field to save all the dynamic information if they are not for searching.

In case I want to let user search on all fields, I have created a DynamicField Model to hold all dynamically created features.

But imagine I have 1,000,000,000 lexicon, and if one create a dynamic feature for every lexicon, this will result creating 1,000,000,000 rows in DynamicField model.

So the sql search function will become quite inefficient while a lot of dynamic features created.

Is there a better solution for this situation?

Which way should I take?

  1. searching for a better db design for dynamic fields

  2. try to tuning mysql(add cache fields, add index ...) with current db design

© Stack Overflow or respective owner

Related posts about mysql

Related posts about ruby-on-rails