Jump to content
Larry Ullman's Book Forums

Recommended Posts

Larry, in part 3 of your "Going Big" series of articles (http://blog.servint.net/2013/09/09/going-big-website-part-3-databases-scale/), you talk about indexing just part of a column. Specifically, you say:

 

For example, if queries might sort by last name, you could index just the first few characters of the last name column.

 

How do you do this?

Thanks.

Link to comment
Share on other sites

You can find some good information here: http://dev.mysql.com/doc/refman/5.0/en/create-index.html

 

To give you an example on last name, you could do something like this:

CREATE INDEX partially_lastname_index ON users (lastname(5));

Keep in mind that you are mainly saving diskspace limiting the index. Unless that's important, you'd generally want to index the whole column. I trust that you've already done some research though. :)

  • Upvote 2
Link to comment
Share on other sites

Actually, it's a speed performance issue. Databases tend to deal with fixed-length columns faster. If you create an index of a limited length, then queries that use that index would treat it as if it were fixed length. An index on the first 5 characters of the last name would be much faster, because it's only looking at those five characters and it's consistently looking at those five characters. The issue, of course, is whether 5 characters (or whatever) is a sufficient length.

  • Upvote 2
Link to comment
Share on other sites

 Share

×
×
  • Create New...