Web Technology Experts Notes

Showing posts with label mysql query. Show all posts

Thursday, 12 December 2019

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

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

Choosing Which Version of MySQL
Don't join extra tables in MySQL Query.
Only get N records which are going to used. Don't get extra records from table which you are not using.
Don't fetch extra column from table which are not in use.
Don't add spam records in tables
Don't get it overload any table like 1GB Size, In this case shift data to another table.
Get the understanding of normalization.
Use Explain/DESCRIBE to know about table structure and Query. For Example:

DESCRIBE SELECT * FROM `users` WHERE username='web-tech'

Partition your table (MySQL 5.1). Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key.
Partition can be achieve in three way 1. RANGE, HASH and List/Key.
Build your indexes to match the queries running. For Example:
```
ALTER TABLE `users` ADD INDEX `profile_id` (`profile_id`)
```
Know about MySQL Indexing

Use concatination indexing, if required (When searching like below).

SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';

No indexing on column which not used in search.
Sometime you need to use OPTIMIZE Table. For Example:
```
OPTIMIZE TABLE `users`
```
Used for defragment tables and update the InnoDB fulltext index
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?

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.

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
```

Set the column as Case sensitive,For Example

ALTER TABLE `users` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

Set the Query as case sensitive, For Example

SELECT *  FROM `users` WHERE BINARY `name` = 'raJ'

Sunday, 14 October 2012

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.