You need an index, it should drastically cut the query time. See: http://www.mysql.com/doc/en/CREATE_INDEX.html#IDX1466
Mike Hillyer Dynamergy Software -----Original Message----- From: Steinar Kolnes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 13, 2002 4:50 PM To: Mysql List Subject: Need help how to make Directory system in MySQL with 6.5 mill subscribers ? 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
