List:General Discussion« Previous MessageNext Message »
From:Leonardo Borges Date:July 13 2011 2:52am
Subject:MySQL creating empty index?
View as plain text  
Hey guys,

I'm working on some performance tuning tasks and realized that there was a
missing index in one of my tables so sure enough I added it on my local
database with the following sql statement:

mysql> create index id_idx on users(id);
Query OK, 972064 rows affected (36.77 sec)
Records: 972064  Duplicates: 0  Warnings: 0

This gave me the performance I wanted so I proceeded to create the same
index in our staging database:

mysql> create index id_idx on users(id);
Query OK, 0 rows affected (20.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

See the number of affected rows? It reports zero, even though it shows the
index in the "show create table" result.  It also doesn't speed up the query
at all and it actually performs worse if I FORCE INDEX mysql to use this
one.

Do you guys have any idea as to why creating this index might not be
working?


Thanks,
Leonardo Borges
www.leonardoborges.com

Thread
MySQL creating empty index?Leonardo Borges13 Jul