# MySQL index question

**URL:** <https://www.ruby-forum.com/t/mysql-index-question/191913>\
**Category:** Rails\
**Created:** [July 20, 2010, 4:14pm UTC](https://www.ruby-forum.com/t/mysql-index-question/191913 "2010-07-20T16:14:18Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 20, 2010, 4:14pm UTC](https://www.ruby-forum.com/t/mysql-index-question/191913/1 "2010-07-20T16:14:18Z")

</div>

I am trying to speed up some DB operations and perhaps have gone  
overboard  
with indexes. Does MySQL usually use only one index per query and  
simply  
match keys on the results of the indexed first part? For example:

DELETE FROM tokens WHERE 6813946236211560448 \<= fnv AND fnv \<  
6818449835838930944 AND updated\_at\<‘2010-06-20 14:08:55’ AND  
occurrences=0;

If I understand correctly from the output of:

EXPLAIN SELECT \* FROM tokens WHERE 6813946236211560448 \<= fnv AND fnv \<  
6818449835838930944 AND updated\_at\<‘2010-06-20 14:08:55’ AND  
occurrences=0;

only the index on fnv is used (there are indexes on updated\_at and  
occurrences).

Is this correct?

TIA,  
Jeffrey

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 20, 2010, 4:38pm UTC](https://www.ruby-forum.com/t/mysql-index-question/191913/2 "2010-07-20T16:38:53Z")

</div>

On Jul 20, 3:12 pm, “Jeffrey L. Taylor” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I am trying to speed up some DB operations and perhaps have gone overboard  
> with indexes. Does MySQL usually use only one index per query and simply  
> match keys on the results of the indexed first part? For example:

one one index will be used per table

> Is this correct?  
> hard to say without said output 🙂

Fred

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 20, 2010, 4:57pm UTC](https://www.ruby-forum.com/t/mysql-index-question/191913/3 "2010-07-20T16:57:03Z")

</div>

Quoting Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> > DELETE FROM tokens WHERE 6813946236211560448 \<= fnv AND fnv \< 6818449835838930944 AND updated\_at\<‘2010-06-20 14:08:55’ AND occurrences=0;  
> > hard to say without said output 🙂

mysql\> EXPLAIN EXTENDED SELECT \* FROM tokens WHERE 6813946236211560448  
\<= fnv AND fnv \< 6818449835838930944 AND updated\_at\<‘2010-06-20  
14:08:55’ AND occurrences=0;  
±—±------------±-------±------±-------------------------------------------------------±--------------------------------±--------±-----±-----±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±-------±------±-------------------------------------------------------±--------------------------------±--------±-----±-----±------------+  
| 1 | SIMPLE | tokens | range | index\_tokens\_on\_fnv\_and\_user\_id,updated\_at,occurrences | index\_tokens\_on\_fnv\_and\_user\_id | 8 | NULL | 6153 | Using where |  
±—±------------±-------±------±-------------------------------------------------------±--------------------------------±--------±-----±-----±------------+  
1 row in set, 1 warning (0.00 sec)

mysql\> SHOW WARNINGS;

| Note | 1003 | select `amethyst_production`.`tokens`.`fnv` AS `fnv`,`amethyst_production`.`tokens`.`user_id` AS `user_id`,`amethyst_production`.`tokens`.`occurrences` AS `occurrences`,`amethyst_production`.`tokens`.`clicks` AS `clicks`,`amethyst_production`.`tokens`.`hides` AS `hides`,`amethyst_production`.`tokens`.`ups` AS `ups`,`amethyst_production`.`tokens`.`downs` AS `downs`,`amethyst_production`.`tokens`.`expires` AS `expires`,`amethyst_production`.`tokens`.`token` AS `token`,`amethyst_production`.`tokens`.`created_at` AS `created_at`,`amethyst_production`.`tokens`.`updated_at` AS `updated_at` from `amethyst_production`.`tokens` where ((`amethyst_production`.`tokens`.`occurrences` = 0) and (6813946236211560448 \<= `amethyst_production`.`tokens`.`fnv`) and (`amethyst_production`.`tokens`.`fnv` \< 6818449835838930944) and (`amethyst_production`.`tokens`.`updated_at` \< \_latin1’2010-06-20 14:08:55’))

Jeffrey

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 20, 2010, 5:08pm UTC](https://www.ruby-forum.com/t/mysql-index-question/191913/4 "2010-07-20T17:08:11Z")

</div>

On Jul 20, 3:55 pm, “Jeffrey L. Taylor” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> mysql\> EXPLAIN EXTENDED SELECT \* FROM tokens WHERE 6813946236211560448 \<= fnv AND fnv \< 6818449835838930944 AND updated\_at\<‘2010-06-20 14:08:55’ AND occurrences=0;  
> ±—±------------±-------±------±------------------------------------- ------------------±--------------------------------±--------±-----±---- -±------------+  
> | id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
> ±—±------------±-------±------±------------------------------------- ------------------±--------------------------------±--------±-----±---- -±------------+  
> | 1 | SIMPLE | tokens | range | index\_tokens\_on\_fnv\_and\_user\_id,updated\_at,occurrences | index\_tokens\_on\_fnv\_and\_user\_id | 8 | NULL | 6153 | Using where |  
> ±—±------------±-------±------±------------------------------------- ------------------±--------------------------------±--------±-----±---- -±------------+  
> 1 row in set, 1 warning (0.00 sec)

Yup, that is just using the index on fnv.

Fred

---

<div class="post-metadata">

**Author:** ![Jeffrey\_LSTaylor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ee7513/32.png) [@Jeffrey\_LSTaylor](https://www.ruby-forum.com/u/Jeffrey_LSTaylor)\
**Post date:** [July 20, 2010, 5:17pm UTC](https://www.ruby-forum.com/t/mysql-index-question/191913/5 "2010-07-20T17:17:33Z")

</div>

Quoting Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> Yup, that is just using the index on fnv.
> 
> Fred

Fred,  
Thank you. Deleting the other indexes should speed up writes.

Jeffrey