Re: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?
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 ( idbigint unsigned not null auto_increment primary key, subscr_id bigint unsigned, telco_id int unsigned, first char (80), last char (40), address char (40), postcodeint 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
RE: Need help how to make Directory system in MySQL with 6.5 mill subscribers ?
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_idint unsigned, first char (80), lastchar (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
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), addresschar (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
