MySQL Forums
Forum List  »  General

Re: How should I make text column search faster?
Posted by: Rick James
Date: September 16, 2011 07:42PM

Having just a UNIQUE(hash) index should suffice. As you describe things, there would be no need for any kind of index on the address, itself.

In MyISAM a 16-byte, NOT NULL, index would take about:
16 bytes -- the hash
6 -- pointer to the data
1? -- overhead
~40% -- waste in BTree
1% -- non-leaf nodes
Total: about 32 bytes per row.
x 180M rows
= 6GB for the .MYI file (or at least for that one index)

If you have 16GB (or more) of RAM, you could probably allow key_buffer_size=6G, thereby having (eventually) no disk reads to fetch rows of the index. Still, fetching the data block would usually take one disk hit.

Bottom line: You might be able to sustain 50 INSERTs/SELECTs per second in December.
For comparison: 1M rows/day == 12/second
What is your expected traffic?

With 4GB of RAM, you probably should not have the key_buffer more than 1.5G. This would lead to about 0.75 (1 - 1.5 / 6) extra disk hits per row access, just for the leaf node of the hash index. (The non-leaf nodes would probably be very cached, occupying perhaps < 100MB. Rule of thumb: non-leaf:leaf :: 1:100.)

May I ask where you are getting a zillion addresses?

Options: ReplyQuote


Subject
Written By
Posted
Re: How should I make text column search faster?
September 16, 2011 07:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.