Jump to content
Larry Ullman's Book Forums
Sign in to follow this  

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.

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...