Re^2: OT - SQL choosing a schema for index tables

[bangor]

Yes but the problem is that these fields have multiple values, and I don't think I can create a usable index on a column that has more than one value.

[poj]

SELECT id from tree_field_index 
  WHERE ( (field='usage' AND name='cider') 
  OR      (field='usage' AND name='juicer') ) 
  AND     (field='flowering' AND name='spring') 
  AND     (field='ripe' AND name='autumn')

This won't work. The select statement gets applied to each record in turn.You can't have a single record where field='usage' AND field='flowering' so the result will be nothing.

Which database are you using, and how many records roughly are you dealing with ?

poj

[bangor]

Thank you poj, you are of course correct, and my Option 2 will not work as I had hoped - you have saved me some time there. I am using MySQL and in this table there are about 200 rows.

[poj]

I should point out that you could make it work but it would be rather complicated !.
Alternatively you could retain the multiple values and use REGEXP in the SELECT . For example

Read more... (2 kB)

Or you could select the records using the single value fields where possible and then filter them with a perl routine using regular expressions.

poj