From: Fred Lindberg Date: March 21 1999 4:41pm Subject: Re: performance List-Archive: http://lists.mysql.com/mysql/714 Message-Id: <19990321164314.593.qmail@id.wustl.edu> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit On Sun, 21 Mar 1999 08:13:46 -0000, Adam Powell wrote: >Does this look as optimised as it can get or am I doing something wrong, as >its causing the server load to be extraordinarily high. 1. if you don't need it indexed, don't index it. Creating the index entry takes a lot of resources. 2. Pack the data. You need only 4 bytes for the IP address. Use a 32-bit int, and convert a.b.c.d to entry = (((a << 8) + b) << 8 + c) << 8 + d. To decode, do: d = entry & 0ffx; entry = entry >> 8; c = entry & 0ffx; entry = entry >> 8; b = entry & 0ffx; entry = entry >> 8; a = entry & 0ffx; Or something like that. You now use 4 bytes rather than 16. Both data and index get much smaller, and comparisons are much simpler (no prefix compression, case-sensitivity, sort order). 3. Make your buffers such that the entire table or at least index fits in memory. Obviously, also much easier with 4 bytes than with 16. 4. If you put the IP string in as is, you may get difficulties, i.e. 001.001.001.001 = 1.1.1.1 etc. -Sincerely, Fred (Frederik Lindberg, Infectious Diseases, WashU, St. Louis, MO, USA)