I had a discussion with a colleague on how to persist a sorted (linked?) list in MySQL, and would like to write down my thoughts on this since this is likely a problem I will face in the future. There must be a better way of thinking about this than I’ve come up with. If you have an idea, don’t hesitate to send me an email. Problem description We have a sorted list of a generic length....
A simple docker command to start MySQL on a tmpfs. Things to note: Mounting localtime affects MySQL server timezone You need to have a my.cnf at the given location ($HOME/docker/mysql/my.cnf) You probably need to create /var/lib/mysql before running the command Setting sql_mode="" is probably not needed for most applications. sudo docker run -d \ --name mysql7 \ -v/usr/share/zoneinfo/Europe/Stockholm:/etc/localtime:ro \ -v$HOME/docker/mysql/my.cnf:/etc/my.cnf \ -e MYSQL_ROOT_PASSWORD=root \ -e MYSQL_ROOT_HOST='%' \ -p3306:3306 \ --mount type=tmpfs,destination=/var/lib/mysql \ mysql/mysql-server:5....
When you have an international database, how do you make sure that the collation is correct for all locales? Here are some thoughts. Note that you have to be careful when mixing collations in queries or SQL will complain. Table with virtual, generated columns CREATE TABLE `note` ( `id` bigint NOT NULL AUTO_INCREMENT, `text` varchar(256) DEFAULT NULL, `text_swedish` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci GENERATED ALWAYS AS (`text`) VIRTUAL, `text_danish` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci GENERATED ALWAYS AS (`text`) VIRTUAL, ....
Things I have learned from troubleshooting mySQL queries Links Index och prestanda i MySQL Join types (from explain) MySQL Profiling Commands # Run query without cache SELECT SQL_NO_CACHE * from users where name like 'Ol%'; # Profiling SET SESSION profiling = 1; SHOW PROFILES; SHOW PROFILE FOR QUERY 3; SHOW STATUS LIKE 'Last_Query_Cost'; How to index/optimize Optimize OR Example: SELECT * FROM users where customerNumber='qwe' OR birthDate='2020-01-01'; You need an index for each OR, which will create a merge index on modern innoDB....
Starting mysql # Same username, no password mysql -p # Password protected mysql -u user mysql -h hostname Create user CREATE USER me GRANT ALL PRIVILEGES ON database_name.* TO 'me'@'%'; ALTER USER 'me'@'%' IDENTIFIED BY 'newPass'; Connect to databaes SHOW DATABASES; SHOW DATABASES LIKE 'ok%'; USE mysql; See database tables SHOW TABLES; SHOW TABLES LIKE 'ok%'; SELECT * FROM important_stuffs; Extend tables ALTER TABLE important_stuffs ADD description VARCHAR(100); ALTER TABLE important_stuffs ADD description VARCHAR(100) AFTER severity; ALTER TABLE important_stuffs ADD description VARCHAR(100) FIRST; Delete from tables SELECT * FROM important_stuffs WHERE severity="low"; DELETE FROM important_stuffs WHERE severity="low"; Insert into tables INSERT INTO important_stuffs VALUES("Wow, so important", "high"); INSERT INTO important_stuffs SET description="Wow, so important", severity="high"; Show access rights SHOW GRANTS; Change all email addresses to my (when mailserver is activated) UPDATE users SET email= CONCAT('me+', id, '@iix....