Gossamer Forum
Home : Products : DBMan SQL : Discussion :

My db running very slow! HELP Please!

Quote Reply
My db running very slow! HELP Please!
When there are 2 people trying to access my guestbook db, the speed slows to a crawl. Any more than 2 and you might as well not have the damn thing at all! I'm working on indexing a few of the keys hoping this will speed things up (I had nothing indexed before). What else can I do? I'm assuming it's not the DBManSQL interface, but the database itself. It contains 33,750 records with an .ISD file size of 26MB. I've looked at many MySQL doc sites and also have the O'Riley book "MySQL and mSQL" trying to find ways of improving performance. I'm trying them all but nothing seems to be working. I'm pulling my hair out!!!! If anyone has a db anywhere close to the size of mine and everything seems to be running fine for you with multiple users accessing the data, please help! Or anyone with any suggestions are greatly appreciated. With all the time I've spent on this project, I don't want to have to scrap it and start over with something else!

AJ

Quote Reply
Re: My db running very slow! HELP Please! In reply to
Just out of curiousity...how many fields do you have in the guestbook database???

One suggestion is creating two tables:

1) Users
2) Comments

If you have a "complex" guestbook with many fields, that could be one of your problems.

Regards,

Eliot Lee
Quote Reply
I may have fixed most of the speed problems... In reply to
I may have fixed most of the speed problems. It still could be a bit faster, but now it works fairly well with 30+ users logged in and searching at once.

I have 21 fields in the database:

%db_def = (
ID => [0, 'INT', -1, 8, 1, '', '', 0],
Date => [1, 'DATE', -1, 11, '', '', '', 0],
Email => [2, 'VARCHAR', -1, 40, '', '', '.+\@.+\..+', 0],
City => [3, 'VARCHAR', 40, 30, '', '', '', 0],
State => [4, 'CHAR', 40, 2, '', '', '', 0],
Country => [5, 'VARCHAR', 20, 20, 1, 'USA', '', 0],
Rank => [6, 'VARCHAR', 0, 10, '', '', '', 0],
Last => [7, 'VARCHAR', 40, 30, 1, '', '', 0],
First => [8, 'VARCHAR', 40, 30, '', '', '', 0],
MOS => [9, 'VARCHAR', 10, 10, '', '', '', 0],
Served => [10, 'VARCHAR', 6, 6, '', '', '', 0],
Vietnam => [11, 'VARCHAR', 5, 6, '', '', '', 0],
Serial => [12, 'VARCHAR', 10, 9, '', '', '', 0],
Boot_Camp => [13, 'VARCHAR', 0, 30, '', '', '', 0],
Platoon => [14, 'VARCHAR', 5, 5, '', '', '', 0],
Grad_Month => [15, 'CHAR', 0, 3, '', '', '', 0],
Grad_Year => [16, 'CHAR', 4, 2, '', '', '', 0],
Duty_Status => [17, 'VARCHAR', 0, 10, '', '', '', 0],
Stationed => [18, 'VARCHAR', 70, 250, '', '', '', 0],
Comments => [19, 'VARCHAR', 70, 250, '', '', '', 0],
Userid => [20, 'VARCHAR', -2, 16, '', '', '', 0]
);

All the fields that are VARCHAR() used to be just CHAR(). By changing to the VARCHAR() I have got the size of the database down from 26MB to 8MB! For those that don't know, but should (like I found out) a CHAR(250) would force the size of the field to be always 250 characters. For example, if you just had "12345" in the field, the field would be written to the db as "12345(+245 spaces)" to make it 250 characters. The VARCHAR(250) will just leave it as "12345". I had the CHAR(250) for 2 fields. That forced 33,750(x2 fields) x250 = 16,875,000 (17MB). Probably 15MB of that were forced spaces!

I also did some tweaking with the mysql host, user, and db tables. It's running fairly smooth now. I have created a script that will create a login account for each entry in the guestbook table that will generate a random unique username, along with a password, then send an e-mail to the user with their account information. When they come to the guestbook, they can sign in and then change their username and password (another mod I made). If anyone would like the account generator mod please let me know. The only problem is it has to be run from a browser and the browser times out after so long. So I can do about 100-200 accounts at a time before it times out. But it beats trying to figure out a way to attach existing records to an owner.

I have a number of other mods as well for DBMan SQL which I will post as soon as I get all the stuff worked out with my database.

Quote Reply
Re: My db running very slow! HELP Please! In reply to
I would appreciate the account generator mmod; I had the secure password lookup mod working on the flattext db but moving to mysql I can't get it going - perhaps your mod will do the trick.

thanks,

Jim