MySQL Administrator shows that my data length is 435 MB with an index length of 425MB in my two column table with 2,227,884 rows in it as you can see in the CREATE TABLE below. To free up more RAM, I'd like to reduce the size of the index. Using the approach below, I beleive I can change the index length from 425MB to 34MB.
1. Do a MD5 hash on the varchar(255) field which is 16 BINARY bytes long
2. Put the BINARY hash into a 3rd column with data type BINARY(16)
3. Remove the UNIQUE index from the varchar(255) and instead put it on the BINARY(16) field.
I get the 34MB index length number by doing:
(16 bytes * 2,227,884 rows) = 35,646,144 bytes = 33.9948 megabytes
Do you agree that this approach should work as planned?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
On another note, you might even consider parsing your referer with parse_url() and separating the major pieces like domain and path out to different columns. Then create a multi-column index on the domain and path - it would probably also be smaller and probably just as fast (and possibly easier to work with). It's hard to say without giving it a shot, though.
Ok, I'm not too worried about the collisions in the MD5. If I cared a lot, I could use SHA256 but that changes the hash length from 16 bytes to 32 bytes.
The idea of this table is to generate a unique key for every referrer - we then use the key in the master log table. Why? Because the key is much smaller than the actual referrer value. In other words, instead of using the 22 byte value "http://www.google.com/" in every row of the master log database, we use the key, "3456" which is a medium integer which is 4 bytes.
So this table must have unique referrers. The master log table however has the same referrer used over and over again.
If you haven't already, also check out MySQLTuner:
http://blog.mysqltuner.com
It also can provide some recommendations on speed and resource issuse.
Business Accounts
Answer for Membership
by: gr8gonzoPosted on 2009-11-06 at 08:16:05ID: 25760433
Theoretically, yes, it would reduce the index size, but I wouldn't put a UNIQUE index on the hash. Given that you have over 2 million rows, there's a decent chance you will have even more rows in the future, and while hash collisions are rare (the result of two different strings having the same hash), they still do happen once in a blue moon. If you put a UNIQUE index on it, you might end up being unable to insert a row because of the hash collision.
Still, indexing the hashes (but not unique) could probably provide you with 1 record almost every time, and SOMETIMES 2, so you would have to adjust your code so that it checks the # of records returned, and then if it's more than 1, you can do another more thorough lookup on the real value.