Mysql – Calculate database size

You can calculate MySql database size using following steps.

Login into mysql :

mysql -h hostname -u username -p

Above command will ask you to enter mysql password.
After log in into mysql, run following query.

SELECT table_schema "DatabaseName",
sum( data_length + index_length ) / 1024 / 1024
"DataBase Size in MB" FROM information_schema.TABLES
GROUP BY table_schema;

Just replace your Database name. Thats it.
Please leave your comments if it worked for you.

Advertisement

How MySQL Uses Indexes

Some MySQL index Facts

• MySQL indexes are used to find rows with specific column values quickly.
• Without an index, MySQL must begin with the first raw and then read through entire table to find rows.
• If the table has an index for the columns, MySQL can quickly determine the position to seek into the middle of the data file without having a look at all data.
• If a table has 1000 raws, this will 100 times faster than reading sequentially.
• Most MySQL indexes are stored in B-Tree.
• The database stores the index value as a B-Tree key, and the record pointer as a B-Tree value.
• Whenever you search for a record holding a certain value of an indexed column, the engine locates the key holding this value in the B-Tree, retrieves the pointer to the record and fetches the record.
• What exactly is a “record pointer”, depends on the storage engine.
  • In MyISAM, the record pointer is an offset to the record in the MYI file.
  • In InnoDB, the record pointer is the value of the PRIMARY KEY.
• In InnoDB, the table itself is a B-Tree with a PRIMARY KEY as a B-Tree key. This is what called a “clustered index” or “index-organized table”. In this case, all other fields are stored as a B-Tree value.
• In MyISAM, the records are stored without any special order. This is called “heap storage”.
• MySQL uses indexes for these operations :
  • To find the rows matching a WHERE clause quickly.
  • To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.
  • To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size.
  • To find the MIN() or MAX() value for a specific indexed column.

I found these useful information on http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html.

Find duplicate records in mysql

When you need to fetch duplicate records from mysql database , run below query in your phpmyadmin interface.

SELECT count('fieldname') AS c FROM 'tablename' GROUP BY 'fieldname1','fieldname1'
HAVING c>1

mysql_affected_rows() :- Returns the number of affected rows in previous MySQL operation.

mysql_affected_rows() is very useful function when we need to know how many rows affected our last MySql operation. This function returns the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE query.

It will return number of rows affected by our last operation & will return -1 if the last query failed. It last query didn’t affect any row of database than it will return 0.

Example :-

<?php
$link = mysql_connect(‘localhost’, ‘milap_patel’, ‘password’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db(‘milap_patel’);

/* this will return the correct numbers of deleted records */
mysql_query(‘DELETE FROM table_name WHERE field_name = “Smile”‘);
printf(“Records deleted: %d\n”, mysql_affected_rows());

?>

Output :-

Records deleted: number of records your query deleted..

 

Index (database)