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