Useful Mysql Queries

 

There are some situations where your application could crash and sometimes the problem is not really in the source code, but in the database. e.g: you are using wrong collation for a specific table or column or maybe you are using wrong data type etc.

These Mysql statements can help you debug or better understand your current database structure.

Let's begin:

1.  Show all columns for a specific table:

 SHOW COLUMNS FROM users;

output:

and also you can filter by field or type etc.

 SHOW COLUMNS FROM users WHERE field = 'email';

2. Show table details:

 SHOW TABLE STATUS;

output:

and of course you can filter by table name:

 SHOW TABLE STATUS where name = 'users';

Output:

3. Show Mysql variables:

 SHOW variables;

output:

and also you filter by variable name:

 SHOW variables WHERE Variable_name =  'max_allowed_packet';

4. Show Table size:

 SELECT TABLE_NAME AS `users`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_NAME = "users" AND TABLE_SCHEMA = 'my_blog'

Output:

More are coming soon..