The best way to speed up your query would be to create a compound index
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
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.
>>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;
>>select * from subscriber where first like 'steinar%' and last like
>>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 ?