Hi Kolnes, Two things:
1. INDEX The number of rows should be immaterial if you have the right indices. In this case, for instance, you search through two different fields so a compound index would make the search much faster: ALTER TABLE subscribers ADD INDEX (first, last) I would suggest adding compound indices on whatever fields you will use for search. 2. WHY "CHAR"? Change the fields to VARCHAR so that the index will maintain parity with only the amount of characters needed. I might be mistaken, but the VARCHAR also helps in saving diskspace because only the actual amount required by a name is allocated to a given record. (Btw, any reason first name should be 80 characters? With a CHAR you'll be losing a lot of space in 6.5 million rows!) 3. RETHINK SOME THINGS If you want to match a certain character in all of the fields of the subscribers table, then you should perhaps consider making a SEARCH table which is tied to the subscribers table with a primary key, or an additional "search" field in the subscribers table itself. This "SEARCH" field would be a blob field and would contain a concatenation of all the information in all the other fields. You could make a FULLTEXT index on this field, and use ONLY this field to do your searches. Also take a look at REGEXP. http://www.mysql.com/doc/en/Regexp.html Hope this is useful, Shashank Shashank Tripathi www.shanx.com | -----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
