List:General Discussion« Previous MessageNext Message »
From:Joseph Bueno Date:August 14 2002 3:50pm
Subject:Re: Need help how to make Directory system in MySQL with 6.5 mill
subscribers ?
View as plain text  
Hi,

The best way to speed up your query would be to create a compound index 
on (first,last):
	create index first_last on subscriber(first,last);
In this case, mysql will use it for both fields instead of doing a 
linear search for the second one.

In order to reduce index size without loosing too much speed, you can 
also index a subset of each field:
	create index first_last on subscriber(first(8),last(8));

Hope this helps
-- 
Joseph Bueno

Tod Harter wrote:
> 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
>>
>>
>>

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