On Tue, 20 Aug 2002, Steinar Kolnes wrote:
> I am going to make a 6.5 mill. phone directory database, and after some
> previous advice I am able to make a search in a fraction of a second.
>
> However there should only be a single search line, not different fields for
> First, Last - name, street address, Zip code and City name.
> The last name field in the database could also contain a company name.
>
> So here is some examples
>
> 1) First Last
>
> 2) First Last City
>
> 3) First Last Street_addr city
>
> 4) First Middle Last Street_addr city
>
> 5) First Middle
>
> 6) Company
>
> 7) Company City
>
> ..
>
> Well, there is a lot of alternatives, so the best might be a freetext.
>
> The database looks like this:
>
> create table subscriber
> (
> id bigint unsigned not null auto_increment primary key,
> subscr_id bigint unsigned,
> telco_id int unsigned,
> typeid int unsigned,
> first varchar (40),
> middle varchar (40),
> last varchar (40),
> address varchar (40),
> postcode int unsigned,
> postname varchar (40),
> phone varchar (12)
> );
>
> My plan was to do the thing below, but the query was very slow:
>
> create table subscriber
> (
> id bigint unsigned not null auto_increment primary key,
> subscr_id bigint unsigned,
> telco_id int unsigned,
> typeid int unsigned,
> first varchar (40),
> middle varchar (40),
> last varchar (40),
> address varchar (40),
> postcode int unsigned,
> postname varchar (40),
> phone varchar (12),
> FULLTEXT (first, middle, last, address, postname, phone)
> );
>
> All suggestions are appreciated, thanks in advance.
Steinar,
Here is a suggestion for you:
Your setup should basically work but try the following (i'm not sure but
it could be faster): add another column such as:
srchwords varchar (217)
and populate it with all words from first, middle, last, address,
postname, phone. Then make your fulltext index over srchwords.
Thomas
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
