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