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

Hi,

It's been a while since I did anything serious with a DB, but couldn't you just create an Index on your main table, rather than creating other tables?

J.C.

  • Comment on Re: OT - SQL choosing a schema for index tables

Replies are listed 'Best First'.
Re^2: OT - SQL choosing a schema for index tables
by bangor (Monk) on Aug 28, 2015 at 01:48 UTC
    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.
[reply]
      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]

In Section Seekers of Perl Wisdom