Thanks to Dan Nelson and Mike Hillyer,
Indexing brought the search downto 2.03 sec compared to 3 min and 16 sec !
Fra: Dan Nelson [mailto:dnelson@stripped]
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
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;
> select * from subscriber where first like 'steinar%' and last like
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
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.
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 <mysql-thread117026@stripped>
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php