Table Op Msg_type Msg_text
IRCDIG.PACKS analyze status OK
Limit CPU Usage
21 posts
• Page 2 of 2 • 1, 2
by Void Main » Mon Feb 13, 2006 1:04 pm
Sorry, poor choice of terms on my part. In phpMyAdmin in the table structure view under the "Size" column, click on the linked size value (22MB, 48.2KB, etc) next to the table you want to work with. You should see a link in the middle of the page that says "Propose Table Structure". What that will do is list the type of data you currently have in the table, max/min lengths, etc and propose what the optimum table structure would be for the data you have in that table.
-
Void Main - Site Admin
- Posts: 5705
- Joined: Wed Jan 08, 2003 5:24 am
- Location: Tuxville, USA
by ZiaTioN » Mon Feb 13, 2006 1:25 pm
Ahh I got it, thanks.
It recommends enumeration type "enum" for some of the old text types that I have now chaged to varchar. For enumeration types I have to list the values? This would not be feasible for my usage since I am unaware of the value prior to adding the data.
It recommends enumeration type "enum" for some of the old text types that I have now chaged to varchar. For enumeration types I have to list the values? This would not be feasible for my usage since I am unaware of the value prior to adding the data.
- ZiaTioN
- administrator
- Posts: 460
- Joined: Tue Apr 08, 2003 3:28 pm
by ZiaTioN » Mon Feb 13, 2006 5:20 pm
Just for reference:
I managed to turn my db into a lean mean indexing machine (with some help here and from experts-exchange)! The biggest enhancement was from changing my heavily used tables to the InnoDB engine instead of the MyISAM engine. InnoDB does row-level locking instead of table-locking. This allowed my application to perform numerous task on the same table at the same time instead of one at a time queueing others and waiting for the lock to be returned.
I did add some indexes too and these seem to be helping a lot too. I first added indexes for the entire length but then changed them to just index the first 5 characters and this seemed to help a ton.
I also changed all my text fields to varchars. Oh and I did find out that you can index text fields, you just have to give a length but I did not use this method anyway.
Now my database runs consistantly around 3% to 11% but does spike to 20% every so often due to the front end searching with wildcards. I guess when wildcards (%) are used, no indexes can be used.
3% is a GIGANTIC improvement over 99.9%.
Thank God for people who know more than I do in certain areas (like databases).
I managed to turn my db into a lean mean indexing machine (with some help here and from experts-exchange)! The biggest enhancement was from changing my heavily used tables to the InnoDB engine instead of the MyISAM engine. InnoDB does row-level locking instead of table-locking. This allowed my application to perform numerous task on the same table at the same time instead of one at a time queueing others and waiting for the lock to be returned.
I did add some indexes too and these seem to be helping a lot too. I first added indexes for the entire length but then changed them to just index the first 5 characters and this seemed to help a ton.
I also changed all my text fields to varchars. Oh and I did find out that you can index text fields, you just have to give a length but I did not use this method anyway.
Now my database runs consistantly around 3% to 11% but does spike to 20% every so often due to the front end searching with wildcards. I guess when wildcards (%) are used, no indexes can be used.
3% is a GIGANTIC improvement over 99.9%.
Thank God for people who know more than I do in certain areas (like databases).
- ZiaTioN
- administrator
- Posts: 460
- Joined: Tue Apr 08, 2003 3:28 pm
21 posts
• Page 2 of 2 • 1, 2
Who is online
Users browsing this forum: No registered users and 1 guest