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…
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 am implementing an entity-attribute-value model to store nationwide property data that will be updated daily: 130 million properties, will probably store billions of values. We also want to search this data on arbitrary attributes, hence using EAV so we…
I've been learning about optimizer hints in MySQL (my version is 8.0.36), and I've been able to experiment with them in very simplified scenarios and it works. However, as soon as I add a nested level, it all breaks down.…
I have a huge table for load to Cloud (BigQuery). Table have very simple structure: CREATE TABLE `my_huge_table` ( `user_id` int(10) NOT NULL, `value_id` int(11) NOT NULL, `some_value` tinyint(2) unsigned NOT NULL DEFAULT '1', `created_at` datetime DEFAULT NULL, `updated_at` datetime…
I have a table: create table accounts_service.operation_history ( history_id bigint generated always as identity primary key, operation_id varchar(36) not null unique, operation_type varchar(30) not null, operation_time timestamp with time zone default now() not null, from_phone varchar(20), user_id varchar(21), -- and…
I have a sql as shown below. SELECT * FROM payment WHERE is_deleted = 0 AND is_privacy = 0 AND ou_code IN ('A1', 'A2', ..., 'W1', 'W2'...) LIMIT 0, 20 There are many elements after in, about 3,000.payment table has…
I have a SQL query when joining several large tables. My database consists of millions of rows across the users, orders and transactions table. Here is the query SELECT u.name, COUNT(o.id) AS order_count FROM users u JOIN orders o ON…
i need to get concurrent sessions for every minute of day from database with ~2 million rows. what is the best(fastest) way to get that data? table contains session_id, session_start_time, session_end_time example CREATE TABLE `session_id_table` ( `session_id` bigint NOT NULL,…
I have two tables: table_a and table_b. Size-wise, table_b is much bigger than table_a. There are fields(names) that are present in both the tables. The goal is to find the names (or the number of names when using count): that…