mysql-8.0 Questions

Count GROUP_CONCAT column to check for duplicates – Mysql

April 12, 2023
SolarNewb
3 Answers

I have the following query: SELECT cpe.entity_id AS configurable_product_id, cpe.sku AS configurable_product_sku, GROUP_CONCAT(DISTINCT cpsa.attribute_id ORDER BY cpsa.attribute_id SEPARATOR ',') AS configurable_super_attribute_ids, cp.entity_id AS simple_product_id, cp.sku AS simple_product_sku, GROUP_CONCAT(cpei.attribute_id, '_', cpei.value ORDER BY cpei.attribute_id SEPARATOR ',') AS simple_product_super_attribute_values FROM catalog_product_entity AS…

VIEW QUESTION

MySQL 8: Query Performance between subquery with GROUP BY and INNER JOIN with GROUP BY

March 19, 2023
Thomas Cheng
2 Answers

I have to retrieve a list of postings, by one or more category IDs. I do not want to have duplicate postings in my results. I'm only interested in responses that directly relate, or can draw relations with MySQL 8…

VIEW QUESTION

Selecting Records Where Value in One Field is the Same As or Later than Value in Another Field – Mysql

March 16, 2023
James
3 Answers

I have a large table called uk that includes fields called CurrTime and EvName. Using the following query (extract below) I create a new field called StartTime that extracts the time from EvName (which is a MEDIUMTEXT field). I want…

VIEW QUESTION

How to clear the database cache in mysql 8 innodb

February 19, 2023
kay
2 Answers

I am attempting to run the same query multiple times on the same mysql 8 database and table. I need to carry out experiments to determine if tweaking the query and or table itself improves performance. However after the first…

VIEW QUESTION

How to partition a table by year and then subpartition by month in mysql 8

February 18, 2023
kay
2 Answers

I have a table that contains a month and a year column. I have a query which usually looks something like WHERE month=1 AND year=2022 Given how large this table is i would like to make it more efficient using…

VIEW QUESTION

How to set the path of 'dump_binary_path' to backup database using spatie package in laravel 9? – Mysql

December 23, 2022
Kmaj
2 Answers

I have a couple of docker containers for my Laravel 9 app (Nginx, PHP, MySQL, PHPMyAdmin, etc). I use spatie package to backup my database. I don't know how to set the path to the mysqldump file as it's in…

VIEW QUESTION

MySQL: 8.0.31 – Large gaps (thousands) in ID Field after few months of usage

December 18, 2022
Darkknight
2 Answers

We see large gaps in primary key column with auto_increment in mysql 8.0.31 release version in multiple tables (with default - innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)). Also I understand some smaller gaps can happen in this mode. We have…

VIEW QUESTION

How to optimize an UPDATE and JOIN query on practically identical tables? – Mysql

November 26, 2022
Hasley
2 Answers

I am trying to update one table based on another in the most efficient way. Here is the table DDL of what I am trying to update Table1 CREATE TABLE `customersPrimary` ( `id` int NOT NULL AUTO_INCREMENT, `groupID` int NOT…

VIEW QUESTION

Mysql – want to get each Groups latest row, group by multiple column

November 8, 2022
techedifice
2 Answers

I need to get the latest row of each combinations of sender_id & receiver_id my messaging table: CREATE TABLE messaging ( msg_id SERIAL, sender_id BIGINT NOT NULL , receiver_id varchar(255) NOT NULL , msg text default NULL, media_link TEXT DEFAULT…

VIEW QUESTION

Using mysql 8 window functions

October 7, 2022
Bisoux
3 Answers

My salary table looks like this, employeeId Salary salaryEffectiveFrom 19966 10000.00 2022-07-01 19966 20000.00 2022-07-15 My role/grades table looks like this, employeeId grade roleEffectiveFrom 19966 grade 3 2022-07-01 19966 grade 2 2022-07-10 I am trying to get the salary a…

VIEW QUESTION