Showing posts with label mysql query. Show all posts
Showing posts with label mysql query. Show all posts

Thursday 12 December 2019

How to get the sizes of the tables in mysql database?

How to get the sizes of the tables in mysql database?


Question: How to get the sizes of the tables in mysql database?
SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC
(Replace mydb with Your Database Name)

Output
How to get the sizes of the tables in mysql database



Question: Get record counts for all tables along with size in MySQL database?
SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)", SUM(TABLE_ROWS) AS "Total Record(S)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC
(Replace mydb with Your Database Name)

Output

Get record counts for all tables in MySQL database


Monday 22 February 2016

MySQL Query Optimization Tips and Techniques

MySQL Query Optimization Tips and Techniques

  1. Choosing Which Version of MySQL
  2. Don't join extra tables in MySQL Query.
  3. Only get N records which are going to used. Don't get extra records from table which you are not using.
  4. Don't fetch extra column from table which are not in use.
  5. Don't add spam records in tables
  6. Don't get it overload any table like 1GB Size, In this case shift data to another table.
  7. Get the understanding of normalization.
  8. Use Explain/DESCRIBE to know about table structure and Query. For Example:
  9. DESCRIBE SELECT * FROM `users` WHERE username='web-tech' 
  10. Partition your table (MySQL 5.1). Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key.
  11. Partition can be achieve in three way 1. RANGE, HASH and List/Key.
  12. Build your indexes to match the queries running. For Example:
    ALTER TABLE `users` ADD INDEX `profile_id` (`profile_id`)
    Know about MySQL Indexing
  13. Use concatination indexing, if required (When searching like below).
    SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';
  14. No indexing on column which not used in search.
  15. Sometime you need to use OPTIMIZE Table. For Example:
    OPTIMIZE TABLE `users`
    Used for defragment tables and update the InnoDB fulltext index
  16. Full Text Search can be used, if required.
    http://www.web-technology-experts-notes.in/2013/05/mysql-fulltext-search.html

Thursday 18 February 2016

How can I make SQL Query case sensitive string comparison on MySQL?

How can I make SQL Query case sensitive string comparison on MySQL

In Websites we display the data which fetch from database using string based conditions.
OR
Login Feature / Change password Featured based on string based conditions.
OR
Show the user detils behalf on user slug like "http://example.com/users/user-slug-value"


If you want to make your SQL Query as case sensitive, We have following 3 ways.

  1. Do at MySQL Server Level
    Set Following in /etc/mysql/my.cnf (Works for >MySQL 5.5 only)
    character-set-server=utf8
    collation-server=utf8_bin
  2. Set the column as Case sensitive,For Example
    ALTER TABLE `users` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
  3. Set the Query as case sensitive, For Example
    SELECT *  FROM `users` WHERE BINARY `name` = 'raJ'



Sunday 14 October 2012

MySQL Delete Table Example

MySQL Delete Table Example

Create a table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


Delete record(s) from table
DELETE FROM users WHERE id=5;
It will delete a record where id is 5

Delete record from two table when they are in join.
DELETE users1,users2 FROM users1,users2,users3 WHERE tusers1.id=users2.id AND users2.id=users3.id


Keep in Mind following points..


  • If we specify the LOW_PRIORITY keyword in query, execution of the DELETE is delayed until no other clients are reading from the table.
  • For MyISAM tables, if we specify the QUICK keyword in query, then the storage engine will not merge index leaves during delete, which  will speed up certain kind of delete.
  • If we specify the IGNORE causes MySQL to ignore all errors during the process of deleting rows.