in reply to Re: OT - SQL choosing a schema for index tables
in thread OT - SQL choosing a schema for index tables

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.
  • Comment on Re^2: OT - SQL choosing a schema for index tables

Replies are listed 'Best First'.
Re^3: OT - SQL choosing a schema for index tables
by poj (Abbot) on Aug 28, 2015 at 07:16 UTC 
    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')

    [download]

    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
[reply]
[d/l]
      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.
[reply]

        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
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom