mysql address book performance problems
I'm looking for some suggestions for improving these tables. They are very inefficient as a result of my inexperience at the time i designed them, and clients who can't make up their minds and have had me hack and modify and add to the application too many times over the last year. The current application is very different from what it was intended to do when I began.
What this is intended to do is allow customers to upload csv mailing lists into pre-defined or one of 5 custom defined columns, dedupe their listcustomers, sort them into groups, and query or sort their list. With 100,000 entries in the listcustomers table, this is very slow. A customer with 5,000 customers will wait 30 seconds to display 250 entries. (some of the problem is hardware, as it only takes 15 seconds on my local dev server, but it's still too long).
One requirement that i'm sure is a contributing factor is that the results must be searchable & sortable by every field in the listcustomers table, and I can't index every field (i don't think, i've read that doing such would defeat the purpose of indexing altogether and actually hurt the performance).
Anyways, here's the table structure. Any suggestions would be appreciated:
# - listcustomers table holds address book entries for all customers
# - column dupecheck is a string based on customerid, firstname, lastname, address and zip that
# should be unique for each listcustomer (used for de-duping)
# looks something like this: 1LKEISE4809VIN10120
# - customfield_# are customer defined fields, the titles stored in table listcustomerfields
CREATE TABLE listcustomers (
listcustomerid INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
listcustomergroupid INTEGER UNSIGNED NOT NULL,
customerid INTEGER UNSIGNED NOT NULL,
remotecustomerid VARCHAR(50) NOT NULL,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
birthdate INTEGER UNSIGNED NOT NULL,
suffix VARCHAR(50) NOT NULL,
company VARCHAR(50) NOT NULL,
department VARCHAR(50) NOT NULL,
title VARCHAR(50) NOT NULL,
address1 VARCHAR(50) NOT NULL,
address2 VARCHAR(50) NOT NULL,
address3 VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL,
state VARCHAR(50) NOT NULL,
zip VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL,
hphone VARCHAR(50) NOT NULL,
wphone VARCHAR(50) NOT NULL,
cell VARCHAR(50) NOT NULL,
fax VARCHAR(50) NOT NULL,
pager VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
customersince INTEGER UNSIGNED NOT NULL,
purchasedate INTEGER UNSIGNED NOT NULL,
purchaseamount FLOAT UNSIGNED NOT NULL,
productid VARCHAR(25) NOT NULL,
active TINYINT UNSIGNED NOT NULL DEFAULT '1',
created INTEGER UNSIGNED NOT NULL,
modified INTEGER UNSIGNED NOT NULL,
notes TINYTEXT NOT NULL,
customfield_1 tinytext NOT NULL,
customfield_2 tinytext NOT NULL,
customfield_3 tinytext NOT NULL,
customfield_4 tinytext NOT NULL,
customfield_5 tinytext NOT NULL,
dupecheck VARCHAR(50) NOT NULL,
INDEX(customerid),
INDEX(dupecheck)
);
# groups or folders created by the customer
CREATE TABLE listcustomergroups (
groupid INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
customerid INTEGER UNSIGNED NOT NULL,
groupname VARCHAR(50) NOT NULL,
created INTEGER UNSIGNED NOT NULL,
modified INTEGER UNSIGNED NOT NULL,
INDEX(customerid)
);
# listcustomer/listcutomergroup associations
# one customer can exist in multiple groups
CREATE TABLE listcustomergroupentries (
listcustomerid INTEGER UNSIGNED NOT NULL,
groupid INTEGER UNSIGNED NOT NULL,
PRIMARY KEY (listcustomerid,groupid)
);
# customer defined field names
# field customfieldnum refers to listcustomers.customfield_#
CREATE TABLE listcustomerfields (
customerid INTEGER UNSIGNED NOT NULL,
customfieldnum TINYINT UNSIGNED NOT NULL,
fieldname VARCHAR(30) NOT NULL
);
mairving posted this at 15:28 — 17th February 2003.
They have: 2,256 posts
Joined: Feb 2001
It looks like your first table could be broken down into several more tables. The other thing is that most of the fields are varchar(50) when they don't need to be. Why would state need to be varchar(50)? Ideally it should be in a separate table, something like state_id int(2) primary key, state_name varchar(20) in the state table and state_id int(2) in the other table.
Keep in mind that good database design is avoiding redundancy in tables. Instead of having 500 records in your main table that say 'Tennessee', you could have 500 records that say 32 or whatever the id of the state is. This also helps to avoid entries in the state field like 'Tn', 'Tenn', Tennessee', 'Tenesee' and the like.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
ROB posted this at 15:32 — 17th February 2003.
They have: 447 posts
Joined: Oct 1999
well, the reason for that is the user is uploading their own address lists, and something like 'state' could be 'FL', 'Florida', 'The Sunshine State', 'where mom lives', or anything for that matter. I can't restrict or modify the data that the user uploads.
mairving posted this at 16:23 — 17th February 2003.
They have: 2,256 posts
Joined: Feb 2001
Sure you can control the data that goes in there. Instead of using a textbox, use a Select list of names pulled from the states table. So the only allowable entries are those that you allow not whatever value they want to put in.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
ROB posted this at 16:26 — 17th February 2003.
They have: 447 posts
Joined: Oct 1999
no no, these lists are imported hundreds or thousands at a time, not entered one by one. user's can export their outlook and excel address books as csv and import them.
mairving posted this at 03:10 — 18th February 2003.
They have: 2,256 posts
Joined: Feb 2001
Well, I guess if I would have read the part about importing their address book, I might have answered better. I don't know how you could make the database any better since it has to be made to import all of the address book garbage. The only way that I could think of to make the data sort faster is to limit the table to a certain number of records. You could possibly do this by having tables based on a alphabetically range. For instance, a table would only contain records A-C, another D-G, etc. That is all I can think about at the moment.
Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states
Mark Hensler posted this at 17:47 — 18th February 2003.
He has: 4,048 posts
Joined: Aug 2000
You could also give each client their own DB.
ROB posted this at 18:02 — 18th February 2003.
They have: 447 posts
Joined: Oct 1999
well, mark, i've considered giving each client their own table, but there will be thousands of individual clients (and currently is over 100) For some reaon 100'sor 1000's of tables (or God forbid databases) just doesnt sit right with me.
Mark Hensler posted this at 01:39 — 19th February 2003.
He has: 4,048 posts
Joined: Aug 2000
You could make an index table, which would have the client info and the name of their table. Then each client could have their own table with their address book data in it.
It may be messy, but it will search a lot faster. And remember, mySQL has limits to table sizes (2GB if I remember right, but it depends on your OS).
Mark Hensler
If there is no answer on Google, then there is no question.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.