How does MySQL store data SO Link Indexes are a very important part of databases and are used frequently to speed up access to particular data item or items. So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to… Continue reading MySql | B + Tree
Category: Mysql
Managing Hierarchical Data in MySQL
Mysql – Having
As per SQL standard - The HAVING clause without a GROUP BY clause acts like the WHERE clause. If the HAVING clause contains no aggregate functions, use the WHERE clause for faster performance. SO Link here's an example of a SQL statement where we use HAVING: select column1 from table1 where condition1 having condition2; isn't it the same exact thing if we do this: select column1 from… Continue reading Mysql – Having
Mysql Basic Questions
What are the main differences between InnoDB and MyISAM? The InnoDB storage engine in MySQL. Support for transactions (giving you support for the ACID property). Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM. Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database,… Continue reading Mysql Basic Questions
Database Sharding
SO Database sharding vs partitioning Sharding partitions spans across multiple database instances. So a table that is sharded has been partitioned, but a table that has been partitioned has not necessarily been sharded. Horizontal partitioning splits one or more tables by row, usually within a single instance of a schema and a database server. It… Continue reading Database Sharding
Mysql Like Query
Link B-Tree Index Characteristics A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes: SELECT * FROM tbl_name WHERE key_col LIKE… Continue reading Mysql Like Query
MySql – Order BY NULL;
By default, MySQL sorts all GROUP BY col1, col2, ... queries as if you specified ORDER BY col1, col2, ... in the query as well. If you include an explicit ORDER BY clause that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. If a query… Continue reading MySql – Order BY NULL;
Schema vs Database
Depends on the database server. MySQL doesn't care, its basically the same thing. In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE. Some other database products draw a distinction. For example, in… Continue reading Schema vs Database
Mysql Deadlock
Docs https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html shared lock A kind of lock that allows other transactions to read the locked object, and to also acquire other shared locks on it, but not to write to it. The opposite of exclusive lock. shared (S) lock exclusive lock A kind of lock that prevents any other transaction from locking the same row.… Continue reading Mysql Deadlock
Char vs Varchar – Mysql
The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained. The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can… Continue reading Char vs Varchar – Mysql
Lets Code
You must be logged in to post a comment.