List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 13 2002 11:13pm
Subject:Re: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?
View as plain text  
In the last episode (Aug 14), Steinar Kolnes said:
> I have to make a large 6.5 million names and numbers database in
> MySql(maybe not so large for some of you). Yet it is very simple,
> here is my sql file:
> 
> create table subscriber
> (
>     id         bigint unsigned not null auto_increment primary key,
>     subscr_id  bigint unsigned,
>     telco_id   int unsigned,
>     first      char (80),
>     last       char (40),
>     address    char (40),
>     postcode   int unsigned
> );
> 
> NB I also merged first and middle names into one "first";

You'll probably want to split them back out.  That means you can't
index the middle name all by itself (if you ever needed to), and it
means that even if you only want to index the first name, you have to
pull in all the middle-name data as well.

Also change your CHARs to VARCHAR; you're probably wasting a lot of
space in the table.  What percentage of first names are over 15
characters, let alone 40?
 
> All the above should be searchable. I have a separate table that take
> cares of postcodes and post names.
> 
> However the search is very slow. It takes more than 3 minutes for a
> query to search for first and last name;
> 
> Example:
> select * from subscriber where first like 'steinar%' and last like 'kolnes%';

That's because you're doing a full table scan of a table that's at
minimum 6.5MB * 180 = 1.1gb :)  Although that only comes out to a data
rate of 7MB/sec; mysql should have been able to scan much faster than
that.

If most of your searches are likeyour example, try creating an index on
the first 5 characters of first and last.  Also create a separate
index on last.  Searches on firstname or first+last will use the
combined index; searches on lastname will use the lastname index.

ALTER TABLE mytable 
ADD KEY ( first(5), last(5) ), 
ADD KEY ( last(5) );

Also run EXPLAIN on some test queries.  Tf the 'rows' column is still
too large or the 'key' column is NULL, try indexing the entire
first+last field (i.e. drop the (5) from the ADD KEY clause).

> Is there any out there that have an suggestion how I can speed things
> up, even if I increases the size to more than 10 mill.
> 
> I planned to have separate tables for first, last and street
> addresses, however is this a good idea ?

You mean separate lookup tables?  That would definitely save space
since you will have a lot of duplicate entries.  It will complicate
searches, though, since you will have to join your primary, first, and
last tables together for the search.  It also makes modification of a
single record a lot harder (since you may have to add records to the
secondary tables if a name changes).  I'd say leave it as one table.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Need help how to make Directory system in MySQL with 6.5 mill subscribers ?Steinar Kolnes14 Aug
  • RE: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?Mike Hillyer14 Aug
    • RE: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?Shashank Tripathi14 Aug
  • Re: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?Dan Nelson14 Aug
    • Thanks SV: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?Steinar Kolnes14 Aug
  • Re: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?Tod Harter14 Aug
  • Re: Need help how to make Directory system in MySQL with 6.5 millsubscribers ?Joseph Bueno14 Aug