New answers tagged

-1

I think you are computing Avg and count twice in select and having clause. Instead, you can use efficacy and uses in having as HAVINg eefficacy >0 AND uses>=10


0

UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND FIND_IN_SET( branch_id, (23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,218,220,221,222,223,224,226,231,232) ) > 0


0

You could try creating yourself a temporary table, inserting your branch numbers into it, and then performing the UPDATE using a JOIN, like so: CREATE TEMPORARY TABLE branch_numbers ( id INT NOT NULL PRIMARY KEY ); INSERT branch_numbers (id) VALUES (23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36) ,(37),(38),(39),(40),(42),(43)...


1

Understanding -- I can't explain the details, but I think this is the big clue: "lock_mode X locks gap before rec insert". Avoiding Are there other statements in each transaction? They may have impact. The two INSERTs are hitting the same pair of columns that you are SELECTing FOR UPDATE: (1294, 26396574) You are only providing a conflicting token for ...


2

That sounds like the answer: Upgrade the OS to 64-bit to run the 64-bit version of MySQL. Interestingly, there is a limit to the size of an InnoDB table in Windows 32 Note Bulletpoint 10 of MySQL Documentation on "InnoDB Limits" Tablespace files cannot exceed 4GB on Windows 32-bit systems (Bug #80149). Note the excerpt from that bug report (submitted ...


0

The reason for that error is that mysql can't locate the keyring_file.dll file. To fix that do the ff: 1. Edit my.ini in C:\ProgramData\MySQL\MySQL Server 5.7\ and add the ff in [mysqld] block: early-plugin-load=keyring_file.dll keyring_file_data=C:/Program Files/MySQL/MySQL Server 5.7/lib/plugin/keyring_file restart the the mysql: net stop MySQL57 net ...


1

Basic Google-Fu produced the following information: The innodb_io_capacity variable defines the number of I/O operations per second (IOPS) available to InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer. I wouldn't set this to the maximum IOPS value of your SSD disk as you need IOPS for InnoDB ...


1

The WITH CONSISTENT SNAPSHOT modifier starts a consistent read for storage engines that are capable of it. consistent read A read operation that uses snapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time. With SERIALIZABLE isolation level you don't ...


0

This is mainly a conjecture on my part, but the technical reason stems from when a SERIALIZABLE transaction goes into effect. According to Paragraph 4 of the MySQL 5.7 Documentation on Transaction Isolation Levels You can enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID compliance is ...


0

Point 1 : Locking Management You must check which processes are staying in locking state & what you can do it for locking i.e creating index for faster retrieval ... You can check deadlock in show engine innodb status Point 2: Redo Log Buffer & Files Make sure the redo log buffer & files are big enough to capture many transaction. Point 3: ...


2

danblack's post answers the main question about the best index strategy for you queries. However, I would add a sometimes forgotten optimization of the index strategy, which is implemented in most recent versions of RDBMs (MySQL, MariaDB, PostgreSQL...): covering indexes Definition of covering index: (from MySQL documentation) An index that includes all ...


1

MySQL reference (country=X) elements should be before range (attraction like 'asd%') in an index. To cover query4 a single attraction index is needed. An index that covers query1 and query2 would be (country, attraction). An ideal query3 index would be (country, state, city, attraction), however if the country, attraction query sufficiently narrows the ...


0

From MySQL 8 , the transaction write-set extraction algorithm is introduced which is XXHASH64. The new default will make it easy for users to enable binary log write-set parallelization on master to speed up group replication.[1] If you are not using MySQL Group Replication, then you can change the settings from XXHASH64 to OFF. And you can enable binary ...


0

There is no advantage in having the buffer_pool_size much more than the total amount of data and indexes you have. (Cf, SHOW TABLE STATUS) The buffer_pool_size should not be so large that it leads to swapping. Swapping is terrible for performance. Note: If you are running other apps on the same box, you need to take their memory usage into account. The ...


0

It may not be possible to eliminate all deadlocks; so write code to check for deadlocks and replay the transaction that gets such an error. Many deadlocks can be avoided by speeding up the queries in question. Let's look at what is slowing down the IODKU you have presented. Having multiple unique indexes aggravates the situation. This is because it must ...


0

It's good you don't see a lot of reads, ideally it should be zero if available memory permits. When InnoDB needs a page it checks the buffer pool. If the page is in the buffer pool - done. If not - InnoDB will read it from the disk and cache it in the buffer pool. So, if the data set fits into the buffer pool there will be eventually no reads from disk. If ...


0

The sentence about requiring a checkpoint before the log file rotation/reuse may have been removed from documentation, but the logic hasn't changed in version 5.6 -- you cannot overwrite log records that belong to not-yet-persisted data, so you have to checkpoint still. The fact that it is happening in your environment indicates that flushing of dirty pages ...


0

You or someone else probably updated innodb_log_file_size variable, if so, you can try rolling back that change to the previous value. If not remember the previous value, try deleting these two files: ib_logfile0 and ib_logfile1 They should be located in the datadir (By default: /var/lib/mysql). Then try to start the service again.


0

Is it possible to recover deleted rows from ibdata1? Probably not. My purpose is to learn how to recover tables and data from ibdata1 file. You should not be trying to do so. That file is in a proprietary format might could, potentially, change between DBMS versions. In order to Restore anything about a database, you should be working with the ...


Top 50 recent answers are included