3

Here's how to identify which 'bad' query to fix that will give you the most bang for the buck: The slowlog plus pt-query-digest. More details here: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog I rarely need to go down the list more than 3 items to make a dramatic improvement. If you are weak on knowing what indexes to add, don'...


3

This below worked for me: SELECT count(*) INTO @exist FROM information_schema.columns WHERE table_schema = 'mydatabase' and COLUMN_NAME = 'mycolumn' AND table_name = 'mytable' LIMIT 1; set @query = IF(@exist <= 0, 'ALTER TABLE mydatabase.`mytable` ADD COLUMN `mycolumn` MEDIUMTEXT NULL', 'select \'Column Exists\' status');...


3

I know this is an old one but it is not valid anymore, with MySQL >= 8.0.17 cf MySQL Function Found_Rows() So we still have to issue both queries ie. add one with the COUNT(), unfortunately.


2

This nice post explains it well. The deadlocks occur due to gap locking done by mysql. There are several reasons for gap locking, and in this particular case, it has to do with preserving a unique key constraint on an index. The situation presents itself to us this way: There is a unique key constraint on a column and we are doing an insert. Mysql ...


2

The only way to avoid such deadlocks is to make sure that all data modifying statements process the rows in the same order, e.g. in ORDER BY uid, job_id order. With the INSERT statements that can be easily done, but with the UPDATE statements it depends on the execution plan the database uses. Perhaps you can do something by using certain indexes and ...


2

For performance, don't try to do it all in one statement. Avoid non-sargable expressions, such as (expires + INTERVAL 5 DAY) >= NOW(); instead say (expires >= NOW() - INTERVAL 5 DAY) Probably it would be better to do 3 queries to test for the 3 possible dups (bundle_id, etc). If it would be OK to pick the newest expired bundle_id dup without seeing ...


2

Instead of doing 3 separate joins, you could accomplish the same thing by simply using an OR clause (the execution time seems to be ~1-2ms): SELECT DISTINCT u.id FROM user u JOIN translation_revision t1 ON u.id = t1.translator_id OR u.id = t1.proofreader_id OR u.id = t1.reviewer_id GROUP BY u.id LIMIT 30 Other ideas would be to denormalize your data ...


1

Mine is fast, at 1-2 milliseconds (tested across various versions between 5.5 and 8.0, plus MariaDB): SELECT u.id FROM user u WHERE EXISTS ( SELECT 1 FROM translation_revision t1 WHERE u.id = t1.translator_id ) OR EXISTS ( SELECT 1 FROM translation_revision t2 WHERE u.id = t2.proofreader_id ) OR EXISTS ( SELECT 1 FROM ...


1

The mysql client has the init-command option, which does what you want, I think. From the manual: --init-command=str SQL statement to execute after connecting to the server. If auto-reconnect is enabled, the statement is executed again after reconnection occurs.


1

Don't use autocommit=0, you might have forgotten to do COMMIT. Try SHOW PROCESSLIST; again. But this time look for the non-system process with the highest "Time". It may indicate the 'user@host' that forgot to COMMIT (or whatever). Alas, the slowlog does not show a query until it finishes. "Sleep" can occur for an open transaction: BEGIN; SELECT ... FOR ...


1

This query may assist in your search for locking and blocking ids. # IS-tx-wait-block-qrys.sql to help research BUSY systems Last Updated 20191227 wh # if this helps you, please email SHAREABLE comments to info@mysqlservertuning.com # From article at # https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-server-activity/ # 20191227 tested on ...


1

The error is related to the difference between Innodb definition and the frm file definition. It probably occurred because of a crash during an ALTER TABLE or similar statement at some point. To restore the consistency an ALTER TABLE wtb_device_auths FORCE will recreate the table (while exclusively locking it). This recreation should make both the innodb ...


1

Basically 'column1' 'column2' 'column3' is different than 'column1,column2,column3' So, you must compare the whole string: BEGIN IF (col_names = 'column1,column2,column3') THEN . . . If you want to compare each variable separately, then you must pass them separate in your procedure call: Call: call hussian('prstate','table_1','column1','column2','...


1

It is not surprising that temp tables would come into play. Why ??? According to the MySQL 5.5 Documentation on join_buffer_size (Italics mine): The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. ...


1

Mysql (community) Version 8.0.17-1.sles12 - OpenSUSE tumbleweed 2019.10.02 mysql> SET GLOBAL expire_logs_days = 4; ERROR 3683 (HY000): The option expire_logs_days and binlog_expire_logs_seconds cannot be used together. Please use binlog_expire_logs_seconds to set the expire time (expire_logs_days is deprecated) ..


1

The queries are bad Then they need fixing. You could add extra hardware but you will get linear improvements at best, fixing the queries could potentially give several orders of magnitude of improvement by removing huge scans and such. Though I'm only guessing here as we have no information about the actual queries. and there are far too many of them to ...


1

This can be related to Bug 83912. If you run SHOW ENGINE INNODB STATUS you may see transactions like this: ---TRANSACTION 4216198446592, not started sleeping before entering InnoDB In order to fix it: SET GLOBAL innodb_thread_concurrency=0;


1

I could not execute queries because of the "too many columns" error since it brought many columns, I changed the variable internal_tmp_disk_storage_engine to MyISAM and it was fixed.


1

since you are going back to a temporary table and updating rows one by one, you should put an index on the table so the updates go faster. alter table tempnumbers add index(num);


Only top voted, non community-wiki answers of a minimum length are eligible