Rails - Apr 2006 - rails db indexing and through association

Hello,
 Say, I have a pictures table, and there are many objects which can have
pictures, so I define a picturable type. Now, to make the query for pictures
more efficient I should index the picture table using both picturable_type and
picturable_id.
 Is this the correct approach to indexing the table, or can things be done
better? I want the database retrieval operation to be extremely fast (I have
some other types such as votes, which require many queries and the speed of
query retrieval is important).

Thanks in advance for your help and suggestions.

    create_table :pictures do |table|
      table.column :name, :string, :limit => 100
      table.column :content_type, :string, :limit => 100
      table.column :data, :blob
      table.column :picturable_id, :int, :null => false
      table.column :picturable_type, :string, :limit => 10, :null => false
    end
    add_index :pictures, [:picturable_type], :name =>
"pictures_index_picturable_type"
    add_index :pictures, [:picturable_id], :name =>
"pictures_index_picturable_id"

-- 
Surendra Singhi
http://ssinghi.kreeti.com, http://www.kreeti.com
Read my blog at: http://cuttingtheredtape.blogspot.com/
,----
| "War is Peace! Freedom is Slavery! Ignorance is Strength!"
|     -- Orwell, 1984, 1948
`----

On Apr 02, 2006, at 12:08 pm, Surendra Singhi wrote:
>  Is this the correct approach to indexing the table, or can things  
> be done
> better? I want the database retrieval operation to be extremely  
> fast (I have
> some other types such as votes, which require many queries and the  
> speed of
> query retrieval is important).
Surendra,

How many pictures do you expect to store?  Indexes only get used when  
the database expects it will return rows faster than a sequential  
scan.  If your images are large and few, it may actually take longer  
to extract the blob than to find the record containing it.
>     add_index :pictures, [:picturable_type], :name =>  
> "pictures_index_picturable_type"
>     add_index :pictures, [:picturable_id], :name =>  
> "pictures_index_picturable_id"
As I understand it, the pictures table will always be searched on  
both foreign ID and type, so you could create a composite index:

     add_index :pictures, [:picturable_type, :picturable_id], :name  
=> "pictures_index_picturable_type_id"

Ashley

Hello Ashley,

Ashley Moran <work@ashleymoran.me.uk> writes:
> On Apr 02, 2006, at 12:08 pm, Surendra Singhi wrote:
>>  Is this the correct approach to indexing the table, or can things
>> be done
>> better? I want the database retrieval operation to be extremely
>> fast (I have
>> some other types such as votes, which require many queries and the
>> speed of
>> query retrieval is important).
>
>>     add_index :pictures, [:picturable_type], :name =>
>> "pictures_index_picturable_type"
>>     add_index :pictures, [:picturable_id], :name =>
>> "pictures_index_picturable_id"
>
> As I understand it, the pictures table will always be searched on
> both foreign ID and type, so you could create a composite index:
>
>      add_index :pictures, [:picturable_type, :picturable_id], :name
>      => "pictures_index_picturable_type_id"
>
Thanks, this is what I was looking for.

-- 
Surendra Singhi
http://ssinghi.kreeti.com, http://www.kreeti.com
Read my blog at: http://cuttingtheredtape.blogspot.com/
,----
| "All animals are equal, but some animals are more equal than
others."
|     -- Orwell, Animal Farm, 1945
`----