Thanks to Dan Nelson and Mike Hillyer, Indexing brought the search downto 2.03 sec compared to 3 min and 16 sec !
Thanks again. Rgs Steinar Kolnes -----Opprinnelig melding----- Fra: Dan Nelson [mailto:[EMAIL PROTECTED]] Sendt: August 14, 2002 1:14 AM Til: Steinar Kolnes Kopi: Mysql List Emne: Re: Need help how to make Directory system in MySQL with 6.5 mill subscribers ? In the last episode (Aug 14), Steinar Kolnes said: > 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"; You'll probably want to split them back out. That means you can't index the middle name all by itself (if you ever needed to), and it means that even if you only want to index the first name, you have to pull in all the middle-name data as well. Also change your CHARs to VARCHAR; you're probably wasting a lot of space in the table. What percentage of first names are over 15 characters, let alone 40? > 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%'; That's because you're doing a full table scan of a table that's at minimum 6.5MB * 180 = 1.1gb :) Although that only comes out to a data rate of 7MB/sec; mysql should have been able to scan much faster than that. If most of your searches are likeyour example, try creating an index on the first 5 characters of first and last. Also create a separate index on last. Searches on firstname or first+last will use the combined index; searches on lastname will use the lastname index. ALTER TABLE mytable ADD KEY ( first(5), last(5) ), ADD KEY ( last(5) ); Also run EXPLAIN on some test queries. Tf the 'rows' column is still too large or the 'key' column is NULL, try indexing the entire first+last field (i.e. drop the (5) from the ADD KEY clause). > 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 ? You mean separate lookup tables? That would definitely save space since you will have a lot of duplicate entries. It will complicate searches, though, since you will have to join your primary, first, and last tables together for the search. It also makes modification of a single record a lot harder (since you may have to add records to the secondary tables if a name changes). I'd say leave it as one table. -- Dan Nelson [EMAIL PROTECTED] --------------------------------------------------------------------- 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
