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