dynamic searchable fields, best practice?

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 d开发者_如何学编程b design for dynamic fields

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

Another idea might be to use MongoDB and MongoMapper, Thinking Sphinx or Solr. Here is Railscast on how to use Mongo: http://railscasts.com/episodes/194-mongodb-and-mongomapper

I think the best way to do this is to use a name/value pairing instead of dynamic fields. Let me explain using the EAV design pattern

So instead of having something like this:

Table: MedicalRecords
<table>
  <tr>
    <th>Temperature in degrees Fahrenheit</th>
    <th>Presence of Cough</th>
    <th>Type of Cough</th>
    <th>Heart Rate in beats per minute</th>
    <th>Column X</th>
    <th>Column X + 1</th>
    <th>... Column N</th>
  </tr>
  <tr>
    <td>102</td>
    <td>True</td>
    <td>With phlegm, yellowish, streaks of blood</td>
    <td>98</td>
    <td>????</td>
    <td>????</td>
    <td>????</td>
  </tr>
</table>

You would design your table like this:

Table: MedicalRecords
<table>
  <tr>
    <th>Name</th>
    <th>Value</th>
  </tr>
  <tr>
    <td>Temperature in degrees Fahrenheit</td>
    <td>102</td>
  </tr>
  <tr>
    <td>Presence of Cough</td>
    <td>True</td>
  </tr>
  <tr>
    <td>Type of Cough</td>
    <td>With phlegm, yellowish, streaks of blood</td>
  </tr>
  <tr>
    <td>Heart Rate in beats per minute</td>
    <td>98</td>
  </tr>
  <tr>
    <td>Column X</td>
    <td>????</td>
  </tr>
  <tr>
    <td>Column X + 1</td>
    <td>????</td>
  </tr>
  <tr>
    <td>... Column N</td>
    <td>????</td>
  </tr>
</table>

(Tried to get the table tags to work but couldn't Try coping my code into an html file to get the idea.)