im storing down ranks in one of my app, and i frequently do this:
select count(*) from table where score > ?
and ? = the current person's score. i intend to memcache this, and i use an index on this table, is there a good way to index it?
views:
28answers:
2
+1
A:
I would create the index on score
Have a look at CREATE INDEX Syntax
It is recomended though to profile a couple of options, and also take into account that this filed might be regularly updated.
astander
2010-02-15 05:44:55
DESC postfix changed nothing ;-) even if it would work in mysql (this is false).from your link: "An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order. "
zerkms
2010-02-15 05:46:11
OK, i see, that is pretty dull, will remove the desc, but stick with the index on score.
astander
2010-02-15 05:48:35
@astander: read something about search within B-TREE. order changed nothing there (for 1 column indexes)
zerkms
2010-02-15 05:48:53
+1
A:
yep, index with "score" as left-most part will be used with this query
zerkms
2010-02-15 05:45:28