backgroundWhether you're a technology guru or a newcomer to Xiaobai, you will step into the pit of Mysql database without index from time to time. A common phenomenon is that the index is clearly added to the field, but it does not take effect.A few days ago, I encountered a slightly special scenario. The same SQL statement takes effect under s ...
MySQL -- index creation principle
1. Data preparation
Create database and table
create database if not exists testindexdb;
create table student_info(
id int(11) not null auto_increment,
student_id int(11) not null ,
name varchar(20) default null,
course_id int not null ,
class_id int(11) defaul ...
Added by Sephiriz on Thu, 24 Feb 2022 11:23:00 +0200
1, Explain tool introduction
Use the EXPLAIN keyword to simulate the optimizer to execute SQL statements and analyze the performance bottlenecks of your query statements or structures
Add the explain keyword before the select statement. MySQL will set a flag on the query. Executing the query will return the information of the execution plan i ...
Added by cyanblue on Wed, 09 Feb 2022 17:59:09 +0200
Index optimization and query optimization
1, Data preparation
Insert 500000 entries in the student table and 10000 entries in the class table.
Step 1: create a table
CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
Added by gnetcon on Wed, 26 Jan 2022 21:19:06 +0200
Database index failure
Database index MySQL optimization (I) MySQL optimization (II) MySQL optimization (III) MySQL optimization (IV)
Common cases of index failure
or keyword in sql statement;The left column field is not used in the composite index;like starts with%;Type conversion required;There are operations in the index column in w ...
Added by petersen313 on Sun, 23 Jan 2022 13:47:54 +0200
backgroundThe timing scenario of the Internet of things is one of the hottest directions at present. Massive time series data such as vehicle trajectory data, vehicle status monitoring data and sensor real-time monitoring data need to be stored in the database. In general, the following requirements exist in such scenariosData high write, low r ...
Added by rinjani on Sat, 08 Jan 2022 21:14:07 +0200
In the first two articles, we introduced some commands commonly used in daily cluster operation and maintenance from the cluster and node layers. Next, we will continue to introduce several commonly used cluster operation and maintenance API s from the index level.Index related commands1. View basic information of cluster indexGET _cat/indices ...
Added by arhunter on Mon, 03 Jan 2022 20:12:56 +0200
How to create an index
Different types of indexes in MongoDB
1. Single field index
2. Composite index
3. Multi key index
4. Text index
MongoDB index properties
3. Partial index
4. TTL index
Some key points
3. dropInd ...
Added by designergav on Sat, 01 Jan 2022 23:05:27 +0200
Compared with InnoDB, MyISAM supports transactions and row level locks. When it comes to InnoDB locks, you have to mention transactions. So let's first understand some knowledge about transactions
Transaction and its ACID attribute
A transaction is a logical processing unit composed of a group of SQL statements. A ...
Added by techbinge on Tue, 14 Dec 2021 21:28:57 +0200
Common cache algorithms:First in first out (FIFO) queue: first in first out. The data that enters first is eliminated first. Disadvantages: ignore the data access frequency and access times.Least recently used (LRU) : least recently used algorithm, that is, if the data has been accessed recently, the probability of being accessed in the future ...
Added by sparc-e on Thu, 25 Nov 2021 22:44:17 +0200