List:General Discussion« Previous MessageNext Message »
From:Mike Hillyer Date:August 13 2002 10:52pm
Subject:RE: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?
View as plain text  
You need an index, it should drastically cut the query time. See:
http://www.mysql.com/doc/en/CREATE_INDEX.html#IDX1466

Mike Hillyer
Dynamergy Software


-----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




---------------------------------------------------------------------
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-mike=wepsoft.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