# One or two indexes? add\_index :votes, \[:item\_id, :ip\_address\]

**URL:** <https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433>\
**Category:** Rails\
**Created:** [January 18, 2008, 7:38am UTC](https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433 "2008-01-18T07:38:13Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![stedwick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/90ced4/32.png) [@stedwick](https://www.ruby-forum.com/u/stedwick)\
**Post date:** [January 18, 2008, 7:38am UTC](https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433/1 "2008-01-18T07:38:13Z")

</div>

What’s the difference between

add\_index :votes, [:item\_id, :ip\_address]

and

add\_index :votes, :item\_id  
add\_index :votes, :ip\_address

Not just in rails, but in the db as well?

Thx!

---

<div class="post-metadata">

**Author:** ![stedwick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/90ced4/32.png) [@stedwick](https://www.ruby-forum.com/u/stedwick)\
**Post date:** [January 18, 2008, 7:45am UTC](https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433/2 "2008-01-18T07:45:51Z")

</div>

For the first you’re adding the index on the item\_id AND ip\_address  
fields,  
together.

For the second you’re adding them on the fields individually.

On Jan 18, 2008 5:07 PM, Stedwick [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Not just in rails, but in the db as well?
> 
> Thx!
> 
> >

–  
Ryan B.

> **[frozenplague.net](http://www.frozenplague.net)**
>
> This domain may be for sale!

Feel free to add me to MSN and/or GTalk as this email.

---

<div class="post-metadata">

**Author:** ![stedwick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/90ced4/32.png) [@stedwick](https://www.ruby-forum.com/u/stedwick)\
**Post date:** [January 18, 2008, 8:33am UTC](https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433/3 "2008-01-18T08:33:29Z")

</div>

So, like, when would I use one or the other? What’s the benefit of  
having an index on two columns, for example?

Sorry, I guess this is more an SQL question than a rails question.

---

<div class="post-metadata">

**Author:** ![stedwick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/90ced4/32.png) [@stedwick](https://www.ruby-forum.com/u/stedwick)\
**Post date:** [January 18, 2008, 10:21am UTC](https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433/4 "2008-01-18T10:21:30Z")

</div>

On 18 Jan 2008, at 07:32, Stedwick wrote:

> So, like, when would I use one or the other? What’s the benefit of  
> having an index on two columns, for example?
> 
> Sorry, I guess this is more an SQL question than a rails question.

I can only tell you about MySQL, but as far as it goes there’s a big  
difference.  
MySQL will only ever use one index per table, so if you do

Votes.find\_by\_item\_id\_and\_ip\_address  
with 2 separate indices then mysql will pick the one it things is the  
best (most selective) and ignore the other one. With a combined index  
it will find things faster.  
Note that if you have an index on [a,b,c,d] then that index can be  
used as an index for any left prefix (ie it can be used as an index on  
a, [a,b] but not on b alone.  
It will also help you with things like Votes.find :all, :conditions =\>  
[“item\_id = 123 AND created\_at \>= ? and created\_at \< ?”] (if you had a  
combined index on item\_id and created\_at). Similarly it will help  
mysql if you did a select of all Votes with a give item\_id and then  
sorted by ip\_address.

So a combined index can really help things out. The problem is that  
since order matters, there are a lot of possible combined indices: if  
you had 5 columns you were interested in, then you could have 5! (120)  
different combined indices which would probably be a bad idea, so you  
have to pick the ones that matter.

Fred

---

<div class="post-metadata">

**Author:** ![stedwick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/90ced4/32.png) [@stedwick](https://www.ruby-forum.com/u/stedwick)\
**Post date:** [January 19, 2008, 3:50am UTC](https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433/5 "2008-01-19T03:50:20Z")

</div>

Excellent description. I got it now. Thx!

On Jan 18, 4:20 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![stedwick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/90ced4/32.png) [@stedwick](https://www.ruby-forum.com/u/stedwick)\
**Post date:** [January 18, 2008, 8:45am UTC](https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433/6 "2008-01-18T08:45:12Z")

</div>

Using:  
add\_index :votes, [:item\_id, :ip\_address]

Will create a single index on both columns, item\_id first.

This will create an index with higher selectivity and is a better index  
ONLY IF you always use both columns in a select query. With most  
database engines you the index will also effectively work if you just  
use item\_id on its own in a select, but NOT using just ip\_address.

Using:  
add\_index :votes, :item\_id  
add\_index :votes, :ip\_address

Will create two separate indexes and both may be used on their own in a  
select query. Depending on the database engine it may be possible that  
both are used in a select that uses both in the where clause. Combining  
two separate indexes for a single query is highly dependent on the  
database engine you are using, so check the documentation for your  
database. Postgresql and MSSQL server can do it, not sure about MySql.

Cheers,  
Gary.

---

<div class="post-metadata">

**Author:** ![stedwick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/90ced4/32.png) [@stedwick](https://www.ruby-forum.com/u/stedwick)\
**Post date:** [January 19, 2008, 8:37pm UTC](https://www.ruby-forum.com/t/one-or-two-indexes-add-index-votes-item-id-ip-address/126433/7 "2008-01-19T20:37:56Z")

</div>

On Jan 18, 1:20 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> So a combined index can really help things out. The problem is that  
> since order matters, there are a lot of possible combined indices: if  
> you had 5 columns you were interested in, then you could have 5! (120)  
> different combined indices which would probably be a bad idea, so you  
> have to pick the ones that matter.

Another use of a multi-column index is the idea of a “covering” index,  
which contains all the columns in a particular query. Only one or two  
of the index columns are actually used for querying - the other  
columns come along for the ride so that, having found the right index  
entry, there’s no need to go back to the table.

///ark