Let's say we have the following table CREATE DATABASE IF NOT EXISTS humans; USE humans; CREATE TABLE IF NOT EXISTS address ( last_name VARCHAR(255) NOT NULL, address VARCHAR(255), PRIMARY KEY (last_name) ); INSERT INTO address values ("x", "abcd"); INSERT INTO…
I have a Sql database with +2 million rows and growing fast. There aren't many columns, only code, price, date and stationID. The aim is to get the latest price by code and stationID. The query works well, but takes…
I have a MYSQL InnoDB table table with the following columns (table and column names changed): date (PK, DATE) var_a (PK, FK, INT) var_b (PK, FK, INT) rel_ab (DECIMAL) where rel_ab is a column that describes a relationship between 2…
When I use hexdump -C demo.ibd to learn how mysql store data, I meet some problems. This is my demo table. CREATE TABLE `demo` ( `id` int NOT NULL, `age` int NOT NULL, `name` varchar(20000) DEFAULT NULL, PRIMARY KEY (`id`)…
I've noticed that full text searches in MySQL do not seem to speed up when reducing the search pool with additional where clauses or joins. For example: SELECT * FROM `blog_posts` WHERE `id` = 1 AND MATCH (post_body) AGAINST ('+mango…
Suppose I'm a PHP application and I start an innodb transaction with mysqli_begin_transaction and it returns true. Then I do a series of inserts. As far as I am aware --- as a PHP application ---, all inserts went in…
In an attempt to overcome deadlocks on the combat server, I reached a dead end. There are 2 tables: First: create table table_1 ( id int auto_increment primary key, data1 text null, data2 text null ); Second: create table table_2…
I am using mysql innodb database. I thought I had understood the locking mechanism of mysql. But I found an example, which conflicts with my understanding.See the example below (verified in version 5.7.32 with rc isolation level). create table Create…
Having a table such as follows: CREATE TABLE Associations ( obj_id int unsigned NOT NULL, attr_id int unsigned NOT NULL, assignment Double NOT NULL PRIMARY KEY (`obj_id`, `attr_id`), ); this should occupy 16 bytes per row. So the overhead per…
Connection1: BEGIN; SELECT * FROM world.city WHERE ID = 130 FOR SHARE; Connection2: SELECT engine, thread_id, object_schema, object_name, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks; +--------+-----------+---------------+-------------+-----------+---------------+-------------+-----------+ | engine | thread_id | object_schema | object_name | lock_type | lock_mode | lock_status |…