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
