post-page

Optimize your Database

33
responses
by
 
on
April 6th, 2005
in
General

DDR Graph

What a difference a simple cleanup and optimization makes! If you are using PhpMyAdmin, follow these simple steps (after safely backing up your data).

Click on the database you are working with on the left hand side frame (not the table, the database name)
On the right hand column your tables should be listed. Scroll down till you see the “Check all” link.
Click on that link, make sure all database tables are checked and then from the dropdown next to it, carefully select “Optimize table”.

This should perform an analysis, repair and an optimization of your database. Most problems will be solved with this. In rare cases you might have to rebuild indices, but thats a huge topic, too dangerous to perform without looking at individual cases and thus I will not preach it here. (My database corruption was one of those rare cases) Please backup your data before any database manipulation and I will not be resposible for any mistakes or loss of data.

PS: Another fairly certain method of fixing your database/tables with PhpMyAdmin is to export your data, delete your existing tables and then re-import. PhpMyAdmin performs certain optimization tasks on the data that are very beneficial.

heading
33
Responses

 

Comments

  1. Dougal Campbell (35 comments.) says:

    So, any advice on how to go about upgrading a MySQL 3.23.47 server to a newer (4.x) version? I’ve been wanting to to that for a long time, but the last time I looked into it, I seem to recall that it looked complicated. And I’m lazy.

  2. John (2 comments.) says:

    Mark, how often do you optimize your database? Also, what tool are you using to get the above graph?

  3. Mark (118 comments.) says:

    I have way too many and I am in the databases enough that I really cannot say with confidence. The reason for this fix was a full text index created for a plugin that I had mentioned previously (don’t want to bring it up again, not really consequential). However, knowing mysql, every couple of years is a good time table for the casual blogger. Databases such as yours will need much more frequent maintenance.

    This graph is created by a tool called rrdtool that I custom scripted to record and graph various system parameters of my server. What you see there is a weekly graph of processor usage calculated from uptime results.

  4. Mark (118 comments.) says:

    Dougal, im still in the 3 point releases and have not upgraded. :)

  5. Aaron (14 comments.) says:

    rrdtool, eh? Plan on releasing it? ;)

  6. Blitz (1 comments.) says:

    Thank You so much for this – it works like a dream.

  7. Matt Ellsworth (4 comments.) says:

    thanks – this worked great!

  8. Quizzer (1 comments.) says:

    very useful info, i am tryng to optimize my database right now =)

  9. Rachel Ramey (2 comments.) says:

    Thanks! I cut 2+ MB off of my database using your instructions!



Trackbacks/Pingbacks

  1. [...] nema – PTGate ::

    Weblog Tools Collection » Optimize your Database

    http://weblogtoolscollection.c.....r-database Optimização de bases de dados MySQL

    This en [...]

  2. [...] ress blog (or blogs) you may find that your mysql servers response falls off a bit Mark at Weblog Tools Collection has a handy tip to spiff up your databases. [...]

  3. [...] April 6th, 2005 Optimize Your Database Weblog Tools Collection has a tip for optimizing your WordPress data [...]

  4. [...] zed my tables!

    Taking the suggestion at Weblog Tools Collection, I optimized all 120 some tables, so perform [...]

  5. [...] klog” — Zootropo temprano por la mañana

    ¿Queda alguien que no sepa que cada cierto tiempo es conveniente optimizar tu base de datos?

    [...]

  6. [...] aning clutter Filed under: Geekspeak, Website, WordPress — @ 12:08 pm I saw this post over at WLTC and just executed the optimization on mine an [...]

  7. [...]

    davidbisset.com

    Optimize Your Database With PhpMyAdmin I’ve noticed minor speed improvements to large mySQL websites when [...]

  8. [...] 8217;ll be looking at (mostly this is a note to myself). One-Click Backup of WP Database Optimize your database Have a icon showing the local weather. Her [...]

  9. [...] og says he saw a large decrease in MySQL activity when he went and optimized his database. Optimize your Database The easy way to do this is with phpMyAdmin [...]

  10. [...] og says he saw a large decrease in MySQL activity when he went and optimized his database. Optimize your Database The easy way to do this is with phpMyAdmin [...]

  11. [...] ur projects. It’s a fast-forward button for your compiler. (tags: Code Programing) Weblog Tools Collection » Optimize your Database (tags: Database) [...]

  12. [...] find phpMyAdmin. For more information: phpMyAdmin: This is the homepage for phpMyAdmin. WordPress Optimize Your Database: an interesting post describing why [...]

  13. [...] Like any other database, over time the MySQL database that powers your blog can get a bit rough around the edges and would benefit from a spring clean. The graph below shows the improvement WeblogToolsCollection obtained from optimising their databases: [...]

  14. [...] Sedangkan optimisasi table-table databasenya sendiri bisa menggunakan PhpMyadmin, khususnya fitur Optimize Table. Ada artikel menarik tentang ini di sini. [...]

  15. [...] wordpress用久了,数据库渐长,这时候该考虑优化一下数据据了。 Fix Database:安装之后,在后台有会Fix Database的按钮,会查找wordpress里面表存在的错误,比如没有结束的地方。用之前请备份你的数据库。 Database Tuning:作者是德国人,页面是经过google翻译的。原页面在这里,可惜后台是德文的。可以修复和优化数据库。 wp-dbmanager:功能强大,拥有很多phpadmin的功能,清空表,删除表,运行SQL Query ,备份和恢复数据库。作者建议一个月优化一次数据库。 WP-PHPadmin:看名字久知道了,让你在后台运行PHPadmin。 这里也提到如何在PHPadmin里面优化数据库。或者该考虑重写WordPress了。 [...]

  16. [...] the heck on earth I was getting duplicate primary key fields in the database, Rob also pointed me tothis articlee to fix it. Works like a [...]

  17. [...] the heck on earth I was getting duplicate primary key fields in the database, Rob also pointed me tothis articlee to fix it. Works like a [...]

  18. [...] Optimise your MySQL database tables. [...]

  19. [...] Optimizador de tablas: un plugin que tampoco está disponible ya asique os dejo el enlace a las instrucciones para realizar una simple tarea de optimización que puede ayudar bastante en términos de carga de servidor. Puede usarse Wp-DBManager [en] como alternativa que amplia esta simple funcionalidad. [...]

  20. [...] tweak here and a little tweak there but nothing really helped.   Tonight I was looking again and found this and I’m like, “You’ve got to be kidding me!”   It made perfect sense [...]

  21. [...] mache dies nur immer mal, wenn es mir in den Sinn kommt. Deshalb habe ich nur Gefühlswerte. Bei Weblog Tools Collection ist dazu ein wenig mehr nach zu [...]

Obviously Powered by WordPress. © 2003-2013

page counter
css.php