Rails db indexing and through association

Rails
1

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 S.
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
`----

2

On Apr 02, 2006, at 12:08 pm, Surendra S. 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

3

Hello Ashley,

Ashley M. [email protected] writes:

“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 S.
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
`----