List:General Discussion« Previous MessageNext Message »
From:Shashank Tripathi Date:August 13 2002 11:46pm
Subject:RE: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?
View as plain text  
Hi Kolnes,

Two things: 

1. INDEX 

The number of rows should be immaterial if you have the right indices.
In this case, for instance, you search through two different fields so a
compound index would make the search much faster: 

   ALTER TABLE subscribers ADD INDEX (first, last)

I would suggest adding compound indices on whatever fields you will use
for search. 


2. WHY "CHAR"?

Change the fields to VARCHAR so that the index will maintain parity with
only the amount of characters needed. I might be mistaken, but the
VARCHAR also helps in saving diskspace because only the actual amount
required by a name is allocated to a given record. (Btw, any reason
first name should be 80 characters? With a CHAR you'll be losing a lot
of space in 6.5 million rows!)


3. RETHINK SOME THINGS

If you want to match a certain character in all of the fields of the
subscribers table, then you should perhaps consider making a SEARCH
table which is tied to the subscribers table with a primary key, or an
additional "search" field in the subscribers table itself. This "SEARCH"
field would be a blob field and would contain a concatenation of all the
information in all the other fields. You could make a FULLTEXT index on
this field, and use ONLY this field to do your searches. Also take a
look at REGEXP. 
http://www.mysql.com/doc/en/Regexp.html


Hope this is useful,
Shashank


Shashank Tripathi
www.shanx.com




    | -----Original Message-----
    | From: Steinar Kolnes [mailto:steinar@stripped]
    | Sent: Tuesday, August 13, 2002 4:50 PM
    | To: Mysql List
    | Subject: Need help how to make Directory system in MySQL 
    | with 6.5 mill
    | subscribers ?
    | 
    | 
    | 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
 

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