mysql - In a Rails 3.2.13 app, I'm generating an index incorrectly -

in rails 3.2.13 app using query_reviewer gem improve database performance.

the code generating sql is:

@seo_keywords = seokeyword.order("category, keyword") 

it generated following sql warning:

msg: no index used here. in case, meant scanning 64 rows. sql: select sql_no_cache `seo_keywords`.* `seo_keywords` order category, keyword 

so generated following db migration:

  def     add_index :seo_keywords, :category     add_index :seo_keywords, :keyword   end 

the migration added indices table indicated schema:

  add_index "seo_keywords", ["category"], :name => "index_seo_keywords_on_category"   add_index "seo_keywords", ["keyword"], :name => "index_seo_keywords_on_keyword" 

i restarted server, loaded page , got same error. suppose i'm creating index incorrectly?

thanks help.

in cases (and doubtless in rdbmss), can more efficient purposes use index perform order instead of sorting records separate step in query execution.

the major advantage of brings first rows sooner large data sets.

however, has disadvantages of needing linguistic-sort index character ordering situations, , being less efficient in terms of accessing data last rows returned may come later index-assisted order-by.

also, accessing every row of table small number of rows (and not scattered on table largely empty space) efficient full-scan table rather use unique index.

these subtleties difficult take account of, , every method external database attempts provide 100% complete "this indexes need" solution going flawed in way, results need seasoned insider database knowledge.

in case (selecting of 64 rows , ordering 2 columns) might make difference composite index:

add_index "seo_keywords", ["category", "keyword"], :name => "index_seo_keywords_on_category_keyword" 

however if character strings special index may required (i'm no mysql expert i'm afraid).

also, dependent on whether mysql indexes nulls, might need ensure category and/or keyword constrained not nul @ database level.