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)