I have this table using InnoDB as engine: create table if not exists Playlists( UserId bigint unsigned not null, Title varchar(50) not null, IsPublic bool not null, primary key (UserId, Title), foreign key (UserId) references Users(Id) on delete cascade on…
I have a MySQL 5.7 table with 8 million distinct email records. I have another table with about 2 million records with emails that are not distinct. I want to see the emails from the larger table that exist in…
I am looking to optimize a JOIN between tables that uses a small subset of columns in the second table. CREATE TABLE `employees` ( `employee_id` bigint NOT NULL, `manager_id` bigint NOT NULL, `org_id` bigint NOT NULL, `union_id` bigint NOT NULL…
I have a table which links the IDs of war memorials with the IDs of records of soldiers. It has a unique index based on the combination of the two IDs (sample data below). I have recently started storing the…
Here is the query, here al.activated_date column is having timestamp. I want to group by application.id, application_logs.activated date. When i remove DATE(al.activated_date), query excution time is 1.2sec. But when i use it, exceeds 15sec.. SELECT a.id, a.description, DATE(al.activated_date) as activated_date,…
I have the following table: CREATE TABLE `ages` ( `id` tinyint(3) UNSIGNED NOT NULL, `min_age` decimal(4,2) NOT NULL, `max_age` decimal(4,2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; With the following data: INSERT INTO `ages` (`id`, `min_age`, `max_age`) VALUES (1, 4.00,…
I want to output all entries from the database within this calendar week based on a given date. Example: I have entered 2024-11-20 Check whether there are any other entries in the calendar week of this date that should be…
MySQL 8.0.40 Sakila database The following two queries correctly return two columns: Rental date and the total count for each date. SELECT DATE(rental_date) AS 'Rental date', COUNT(DATE(rental_date)) AS 'Count for each day' FROM rental GROUP BY DATE(rental_date); SELECT DATE(rental_date) AS…
I need to generate the following two result sets: Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P),…
Im trying to convert a date in the form either dd/mm/yy with from concertlistts as c SELECT STR_TO_DATE(str(c.Date)), "%e, %m, %y")); but when I try to run it it says syntax errors How do I fix this I've tried from…