MySQL: Quickly Select Data with Multiple Conditions from One Table

Submitted by Peter Majmesku on Fri, 08/04/2017 - 11:09

Selecting data from one table with multiple conditions with Subselects is slow. Joins are quicker. Here is an example:

select * from OrderMetaData
Join OrderMetaData as a on = a.Order_id and = 'store' and a.value = 128
Join OrderMetaData as b on = b.Order_id and = 'processing_status' and b.value = 4;

This query takes 0,44 seconds in a table with > 800.000 entries. The sub-select takes 17 seconds.


MySQL: Write All Executed Queries Into a Log File

Submitted by Peter Majmesku on Tue, 05/23/2017 - 13:54
  1. Open the MySQL config file, which is located at /etc/mysql/my.cnf on Linux systems.
  2. Then write the following lines at the end of that file.

general_log_file = /var/log/mysql/general_mysql.log
general_log = on

Restart your MySQL server (e.g. by executing "sudo service mysql restart" on Ubuntu Linux systems). Now you can follow all new queries as they are executed by this command:

tail -f /var/log/mysql/general_mysql.log

You can leave this program in command line by pressing CTRL+C.

For disabling, just set "general_log" to "off" or comment the out the previous lines in /etc/mysql/my.cnf by writing the # character at the beginng of the lines, save and restart the MySQL server again.

Update: Alternative setting by "general_log" variable

Recently the approach from above was not possible on my MariaDB server in Linux Mint 18.1. The log file was just not written. Even if I have created it. Then I could enable the general_log by variable. For this login into your MariaDB/MySQL server via command line. Then enable the general_log:

SET GLOBAL general_log = On;

Check all your variables which have something to do with "log" by this command:

show variables like '%log%';

The filename of the general_log is stored in that variable: "general_log_file". The default general log file location on Ubuntu/Debian based Linux systems (like Linux Mint) is: 


These commands can help you, to see the setting quickly:

peter@computer ~/Dev/ $ mysql -uroot -ppassword -e "show variables like '%log%';" | grep general_log
mysql: [Warning] Using a password on the command line interface can be insecure.
general_log     ON
general_log_file        /var/log/mysql/general_mysql.log
peter@computer ~/Dev/ $ 


MySQL: Fixing Foreign Key Constraint Errors

Submitted by Peter Majmesku on Wed, 05/17/2017 - 19:12

Login to your MySQL server via command line. Then execute the following statement:


You will get a error message like:

Foreign key constraint fails for table `my-table`.`JobExchange`:
  CONSTRAINT `FK_42760B1EE7A1254A` FOREIGN KEY (`contact_id`) REFERENCES `contact` (`id`) ON DELETE SET NULL
Trying to add to index IDX_42760B1EE7A1254A tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 8000000d; asc     ;;

But the parent table `my-table`.`contact`
or its .ibd file does not currently exist!

This shows you, that MySQL cannot find the corresponding database table. This can be due to lowercase-uppercase mismatch of the table names. Check your MySQL server settings:

show variables like 'lower%';

This will show something like this:

| Variable_name | Value |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
2 rows in set (0,01 sec)

Show detailed information about the primary-foreign key references

mysql> SELECT

| JobExchangePdf | jobexchange_id | FK_7A971E966193B1C5 | jobexchange | id |
| jobexchange_jobemploymentcategory | jobexchange_id | FK_F5E32C9C6193B1C5 | jobexchange | id |
2 rows in set (3,59 sec)

MySQL Workbench Vs. PhpStorm

MySQL Workbench shows the lower-case named table names correctly. PhpStorm does not. PhpStorm is guessing the right table name.

Possible fix

Drop the foreign key constraint and create a new one. F.e. with MySQL Workbench. You can drop the foreign key with the following command:

ALTER TABLE `my-table`.`JobExchange`
ALTER TABLE `my-table`.`JobExchange`
DROP INDEX `IDX_42760B1E6C7B4691` ;

The foreign key will be added like by this command:

ALTER TABLE JobExchange ADD CONSTRAINT FK_42760B1E6C7B4691 FOREIGN KEY (jobtype_id) REFERENCES JobExchangeCategory (id) ON DELETE SET NULL;
CREATE INDEX IDX_42760B1E6C7B4691 ON JobExchange (jobtype_id);

MySQL: Import and Export (Compressed) Databases

Submitted by Peter Majmesku on Mon, 08/29/2016 - 19:24

The export (dump)

This command will generate you a compressed dump file with the date information in the filename.

mysqldump -u username -p"password" database_name | gzip > $(date +"%Y-%m-%d")-database_name.sql.gz

Import the database dump

Firstly make sure that the "empty" database exists. Then import it:

mysql -uUSERNAME -p"PASSWORD" database_name < dump-filename.sql

Import a Gzip compressed database dump file:

zcat database-dump-file.sql.gz | mysql -uroot -ppassword database-name

Your MySQL user has no LOCK TABLES permission

If you receive the error

mysqldump: Got error: 1044: Access denied for user 'my-user'@'localhost' to database 'my-database' when using LOCK TABLES

then your MySQL has not the permission to lock the database tables. This would guarantee, that you get an non-corrupted database dump. Because there were no write-queries while you're dumping the database. You can skip this error and risk a corrupted database dump (which might still work for development purposes) with the --skip-lock-tables option. Here is the full command as an example:

mysqldump -uUSERNAME -pPASSWORD --skip-lock-tables DATABASENAME | gzip > DATABASENAME.sql.gz

Subscribe to MySQL