Results 1 to 8 of 8
  1. #1

    Optimize Mysql Table

    I have a database which has over 5 million rows.

    I want to optimize 1 single row of it, can someone, tell me command for it.

  2. #2

    Solved

    I have solved the issue, But here is information on how to do it.

    Code:
    To optimize a mysql database table via command line you will need to ssh into your server as the root user and enter into the mySQL command line interface by typing "mysql". This will give you the following prompt.
    
    mysql>
    
    Once you are at a mySQL command prompt you will need to type the following. The first selects the database you wish to use and the second optimizes the table(s).
    
    mysql> USE your_database;
    Database changed
    
    
    mysql> OPTIMIZE TABLE `table1` , `table2` , `table3` , `table4`;
    -Thread Closed-

  3. #3

    Update

    What will be the command to optimize "all the tables" of a database ?

  4. #4
    Join Date
    Sep 2006
    Location
    Cardiff - United Kingdom
    Posts
    1,568
    How long did it take you do OPTIMIZE on a table with 5 million rows on it? What's the tables overall size (in MB?) I have a large table with 800k records in it (1 gig in size) and I can't really OPTIMIZE it anymore, it just takes too long.

    To optimize all tables, use something like:

    http://www.totallyphp.co.uk/scripts/...l-database.htm
    Plagiarism Guard - Protect Against Content Theft
    Tristan Perry - Personal blog

  5. #5
    I wonder why you are not using phpMyadmin ?!?

  6. #6
    Quote Originally Posted by tristanperry View Post
    How long did it take you do OPTIMIZE on a table with 5 million rows on it? What's the tables overall size (in MB?) I have a large table with 800k records in it (1 gig in size) and I can't really OPTIMIZE it anymore, it just takes too long.

    To optimize all tables, use something like:

    http://www.totallyphp.co.uk/scripts/...l-database.htm

    I do not need a script , i needed a command line command to optimize all tables of a database.

    Anyways, here it is -


    shell> mysqlcheck [options] db_name [tables]
    shell> mysqlcheck [options] --databases db_name1 [db_name2 db_name3...]
    shell> mysqlcheck [options] --all-databases

    -Closed-
    Last edited by Passwordz; 04-21-2008 at 02:30 AM.

  7. #7
    Join Date
    Sep 2006
    Location
    Cardiff - United Kingdom
    Posts
    1,568
    Actually that only checks all tables. It doesn't optimize them.

    You cannot do "OPTIMIZE TABLE *" etc - you have to do each table one by one (hence the script).
    Plagiarism Guard - Protect Against Content Theft
    Tristan Perry - Personal blog

  8. #8
    Quote Originally Posted by tristanperry View Post
    Actually that only checks all tables. It doesn't optimize them.

    You cannot do "OPTIMIZE TABLE *" etc - you have to do each table one by one (hence the script).
    It does optimize and even repairs all tables , just use this -
    Code:
    shell> mysqlcheck -r -o db_name
    Hope it helps you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •