HartleySan Posted September 19, 2013 Share Posted September 19, 2013 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 More sharing options...
Antonio Conte Posted September 19, 2013 Share Posted September 19, 2013 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. 2 Link to comment Share on other sites More sharing options...
HartleySan Posted September 19, 2013 Author Share Posted September 19, 2013 Thanks for the info, Antonio. It was a matter of curiosity more than anything. I didn't even know you could do that. Link to comment Share on other sites More sharing options...
Larry Posted September 20, 2013 Share Posted September 20, 2013 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. 2 Link to comment Share on other sites More sharing options...
HartleySan Posted September 20, 2013 Author Share Posted September 20, 2013 Thanks, Larry. Link to comment Share on other sites More sharing options...
Recommended Posts