(SELECT 1 `a`) UNION (SELECT 2 `a`) ORDER BY `a` DESC LIMIT 1 is a perfectly valid query in MySQL and as far as I can tell the same as SELECT 1 `a` UNION SELECT 2 `a` ORDER BY `a`…
I have a dataset that tracks user activity, and I need to identify consecutive rows where a specific condition is met. The table structure and sample data are as follows: Table: UserActivity UserID Activity Timestamp 1 Login 2023-11-01 08:00:00 1…
I recently upgraded the character set of my MySQL database from utf8mb3 to utf8mb4. One of my table columns is defined as an ENUM('saved', 'updated', 'deleted') NOT NULL DEFAULT 'saved'. However, after performing the charset upgrade, I noticed that the…
I recently upgraded to MySQL 8.4, and I just noticed my C drive is almost full. After looking into it, I realized that MySQL has been creating a bunch of files called "binlog.XXXXXX" where XXXXXX is a random number (it…
We are upgrading MySQL from 5.7 to 8.0. Table cuatom_shapes has column shape with SRID=0, in which few rows have data with GEOMETRYCOLLECTION and few other rows have data with POLYGON. Though column SRID is set to 0, for POLYGON…
I am trying to use covering index (with a functional index) in MySQL 8.0.28 to speed up a group by query on a large InnoDB table. Covering index created: ALTER TABLE `sp_files` ADD INDEX `ix_site_header_to_cleaned_text2` (`sp_site_foreign_key`, (length(`cleaned_text`))); Using only the…
Apparently, sorting is somehow applied incorrectly in mysql 8 (checked on 8.0.33 to 8.0.35) when query has ORDER BY + GROUP BY + GROUP_CONCAT() + COUNT(*) OVER() window function. See test case below (NOTE that it's synthetic and oversimplified for…
I have an application that runs on PHP 5.6 and Laravel 5.2. It's hosted in AWS on an AWS Linux 1 EC2 machine. This web server connects to a RDS MySQL instance. The RDS instance has been running MySQL 5.7…
SELECT e.frist_name, SUM(e.prince), YEAR(e.hire_date) AS year FROM ( SELECT * FROM employee WHERE termination IS NULL ) e JOIN storage s ON e.id = s.eid GROUP BY e.id, e.hire_date; After SQL query I have a table like this: +--------------+---------------+------+ |…
I have the following query: SELECT match_main.checked, match_main.match_main_id, match_main.updated, match_main.created FROM match_main LEFT JOIN match_team AS mt1 ON mt1.match_main_id = match_main.match_main_id AND mt1.team_number = 1 AND mt1.version_number = 0 LEFT JOIN match_team AS mt2 ON mt2.match_main_id = match_main.match_main_id AND mt2.team_number…