Forum: Ruby on Rails rails db indexing and through association

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Surendra S. (Guest)
on 2006-04-02 15:09
(Received via mailing list)
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
`----
Ashley M. (Guest)
on 2006-04-02 16:01
(Received via mailing list)
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
Surendra S. (Guest)
on 2006-04-02 19:16
(Received via mailing list)
Hello Ashley,

Ashley M. <removed_email_address@domain.invalid> 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
`----
This topic is locked and can not be replied to.