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
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.
Hope this is useful,
| -----Original Message-----
| From: Steinar Kolnes [mailto:steinar@stripped]
| 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;
| select * from subscriber where first like 'steinar%' and last like
| 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 ?
| Steinar Kolnes