On Fri, 07 Dec 2007 16:10:34 -0600, dis <dis@nomail.com>
wrote:

>Hello-
>
>I currently have a large table - 100 million rows, create as such:
>
>CREATE TABLE `result` (
> `id` int(11) NOT NULL auto_increment,
> `hostname` varchar(75) default NULL,
> `ip` varchar(15) default NULL,
> PRIMARY KEY (`id`),
> KEY `ip` (`ip`)
>) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
>
>
>I have 2 questions. My query is like this:
>
>SELECT hostname,ip FROM result WHERE ip = '192.168.1.1' ORDER BY hostnam
>e LIMIT 0,100;
>
>1. Would it be better to have the IP address split in to 4 int(3) fields
>and index each of those? The search seems pretty quick right now, but I
>may make more complex queries on those fields in future.
>
>For example, I may want to search a netblock as such;
>
>SELECT hostname,ip FROM result WHERE ip1 = 192 AND ip2 = 168 AND ip3 IN
>(1,2,3) ORDER BY hostname;

In general it would be better to convert IP addresses into
a single integer with INET_ATON('ip1.ip2.ip3.ip4') and
store that in the database (INT UNSIGNED).
Every time you look for a specific address you have to
convert your arguments to integers as well.

INET_NTOA() converts back to a 'ip1.ip2.ip3.ip4' string.

>2. What is the best way to index hostname? Usually when I search by IP,
>the hostnames are unique enough in just the first 15 characters. I tried
>creating a partial index on hostname(15), but when running EXPLAIN the
>query still uses filesort, and the query still takes forever when
>sorting. I found that if I create a full index on hostname, it worked
>well, but that index takes forever to create.
>
>Would it be better to create another field, like hostnameindex, and
>insert into that field the first 15 chars of each hostname? Then build
>an index on that and sort by that?

I have no ideas about that right now.

>Thanks for any help. I am not exactly a pro at database design.

Neither am I
HTH
--
( Kees
)
c[_] Build a fire for a man and he will be warm for
a day, but set fire to that man and he will be
warm forever! (Sun Tzu) (#443)