MYSQL

SQL Joins

by

There are four main types of joins in SQL: In addition to these four main types of joins, there are also a few other types of joins, such as: The type of join that you use will depend on the specific requirements of your query. For example, if you want to find all customers who … Read more

What is the difference between Optimistic and Pessimistic locking

by

Optimistic and pessimistic locking are two different concurrency control mechanisms used in databases and multi-user systems to handle concurrent access to shared resources, such as database records, files, or data structures. Both mechanisms aim to prevent conflicts and maintain data consistency when multiple users or processes attempt to access and modify the same resource simultaneously. … Read more

Duplicate MySQL Indexes

by

In MySQL, a primary key is a unique identifier for each row in a table. It ensures that each record is uniquely identifiable and helps in enforcing data integrity. When you create a primary key on a column, MySQL automatically creates an index on that column to optimize the search and retrieval of data. Indexes … Read more

Database Normalization

by

Normalization is the process of organizing data in a database in a way that minimizes redundancy and dependency, and thus improves data integrity. There are several levels of normalization, each of which has its own set of rules. To normalize a MySQL database, you can use the following steps: Once you have normalized your database, … Read more

Primary Keys without auto increment (MYSQL)

by

How do you check that all primary keys have an auto increment function? Well here is how to do it. SELECT distinct u.table_name, u.column_name, u.constraint_name, c.extra FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u, INFORMATION_SCHEMA.COLUMNS c WHERE u.TABLE_NAME=c.TABLE_NAME and u.COLUMN_NAME=c.COLUMN_NAME AND u.TABLE_SCHEMA=c.TABLE_SCHEMA AND u.CONSTRAINT_NAME = ‘PRIMARY’ AND u.TABLE_SCHEMA = ‘mytoll’ AND c.extra ‘auto_increment’;

MySQL Deadlocks in InnoDB

by

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds. A deadlock can occur when transactions lock rows in multiple tables (through statements such as UPDATE … Read more

Calculate MySQL Size

by

How to calculate the table and database size in MySQL SELECT table_schema “Data Base Name”, sum( data_length + index_length) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema ; and SELECT table_name, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) “Size in MB” FROM information_schema.TABLES where table_schema = … Read more

Calculate Table Size in MySQL

by

How to calculate all tables in a schema SELECT TABLE_NAME AS “Table Name”, table_rows AS “Quant of Rows”, ROUND( ( data_length + index_length ) /1024, 2 ) AS “Total Size Kb” FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema = ‘YOUR SCHEMA NAME/DATABASE NAME HERE’ LIMIT 0 , 30;