List:General Discussion« Previous MessageNext Message »
From:Tod Harter Date:August 14 2002 3:29pm
Subject:Re: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?
View as plain text  
On Tuesday 13 August 2002 06:50 pm, Steinar Kolnes wrote:

Just create indexes on first and last, that should improve the speed of your 
query drastically. It will of course be a BIG index. You might experiment 
with only making the width of the index small, like maybe 8 or 10 characters 
might be enough to get 99% of the benefit with a lot less size. You might 
experiment with only indexing last names as well, the query optimizer should 
then use that index first and will thus only have to do linear search through 
the resulting candidate records.

I think the design of your table is fine. Note that adding NOT NULL to a 
column's definition saves you a small amount of space per row, and you 
probably don't want nulls anyway. 

> Hi there,
>
> 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";
>
> 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%';
>
>
> 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 ?
>
> Rgs
> Steinar Kolnes
>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <mysql-thread117021@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-tharter=aptusventures.com@stripped> Trouble
> unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
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