How to regex in a MySQL query
I think you can use REGEXP instead of LIKE SELECT trecord FROM `tbl` WHERE (trecord REGEXP ‘^ALA[0-9]’)
innodb
InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes
Did you try the Bulk Data Loading Tips from the InnoDB Performance Tuning Tips (especially the first one): When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET … Read more
Setting up table relations what do “Cascade”, “Set Null” and “Restrict” do?
CASCADE will propagate the change when the parent changes. (If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.) SET NULL sets the column value to NULL when a parent row goes away. RESTRICT causes the attempted DELETE of a parent row to fail. EDIT: You didn’t … Read more
Why is MySQL InnoDB insert so slow?
InnoDB has transaction support, you’re not using explicit transactions so innoDB has to do a commit after each statement (“performs a log flush to disk for every insert”). Execute this command before your loop: START TRANSACTION and this after you loop COMMIT
What are MySQL database engines? [closed]
mysql> SHOW ENGINES; +————+———+—————————————————————-+————–+——+————+ | Engine | Support | Comment | Transactions | XA | Savepoints | +————+———+—————————————————————-+————–+——+————+ | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | … Read more
Should I COUNT(*) or not?
If the column in question is NOT NULL, both of your queries are equivalent. When group_id contains null values, select count(*) will count all rows, whereas select count(group_id) will only count the rows where group_id is not null. Also, some database systems, like MySQL employ an optimization when you ask for count(*) which makes such … Read more
Force InnoDB to recheck foreign keys on a table/tables?
DELIMITER $$ DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$ CREATE PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`( checked_database_name VARCHAR(64), checked_table_name VARCHAR(64), temporary_result_table ENUM(‘Y’, ‘N’)) LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA BEGIN DECLARE TABLE_SCHEMA_VAR VARCHAR(64); DECLARE TABLE_NAME_VAR VARCHAR(64); DECLARE COLUMN_NAME_VAR VARCHAR(64); DECLARE CONSTRAINT_NAME_VAR VARCHAR(64); DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64); DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64); DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64); DECLARE KEYS_SQL_VAR VARCHAR(1024); DECLARE done INT DEFAULT 0; DECLARE … Read more
MySQL InnoDB foreign key between different databases
I do not see any limitation on https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html. So just use otherdb.othertable and you will be good.
How can I rebuild indexes and update stats in MySQL innoDB?
This is done with ANALYZE TABLE table_name; Read more about it here. ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM, BDB, and InnoDB. This statement works with MyISAM, BDB, InnoDB, and NDB tables.
#1025 – Error on rename of ‘./database/#sql-2e0f_1254ba7’ to ‘./database/table’ (errno: 150)
There is probably another table with a foreign key referencing the primary key you are trying to change. To find out which table caused the error you can run SHOW ENGINE INNODB STATUS and then look at the LATEST FOREIGN KEY ERROR section.