All Questions

Tagged with
Filter by
Sorted by
Tagged with
0
votes
1answer
19 views

DML queries with different performance on similar servers

I am measuring the performance between two servers that serve a database via MySQL 5.6. The DB1 server is a dedicated database server and the DB2 server shares resources with other services such as ...
0
votes
1answer
49 views

optimize query — remove nested selects

How can I rewrite the query to increase performance? Here is the query: SELECT flight.*, DAYOFWEEK(flight.flight_time) AS day_of_week, IF(SUM(...
0
votes
1answer
26 views

Mysql Query Optimization [Match Against] [closed]

I have tried all the possible option to optimize the query, but no luck. Query 1: SELECT *, ( 3959 * acos ( cos( radians(51.5073509) ) * cos( radians(...
0
votes
1answer
20 views

Mysql performance -optimization(insert,select,proc) [closed]

Summary How can I receive, process, and insert 1000 rows every 5 seconds? Details I use the database extensively. We have vehicle tracking software. According to this software, a tool adds ...
1
vote
2answers
67 views

How to Speed Up my SQL Query?

I have a query that is taking much longer than I expect it should, and I can not figure out how to speed it up. For a little bit of background on the table structure, these are audited records. There'...
0
votes
1answer
40 views

Optimize slow COUNT in MySQL subquery

I have the following query that takes minutes to complete: SELECT DISTINCT Designation, Model, ( SELECT COUNT( Equipment.EquipmentID ) FROM Equipment INNER JOIN EquipmentDesignation_Vw ...
0
votes
1answer
17 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
1
vote
1answer
22 views

Add new columns on existing or auxiliary table?

I have a database with the following table: stores - ID - name - phone number - email address After adding around 4000 stores to the database we figured that we will need to allow store owners to ...
1
vote
0answers
16 views

Not able to update innodb_ft_num_word_optimize

We are using a AWS-RDS(MySQL 5.7) instance with 16GB RAM. We have a table with millions of entries and a FULL-TEXT index which needs to be OPTIMIZED. Apparently, FULL-TEXT optimization is very ...
-1
votes
1answer
28 views

What is the best DB schema to handle exceptions and prevent redundancy?

There are some products that can be sold in multiple units and the base unit is kilogram. The unit of each product has different weight, for example a Bundle of product A is 20kg but a Bundle of ...
0
votes
1answer
52 views

How to reduce the response time of a query that yields ~4.9 million rows in ~12-13 seconds

I have the following doubt about the execution performance of a query. This query takes about 12-13 seconds, and brings a total of 4,879,049 rows. Could I lessen this query's response time? The query ...
0
votes
0answers
44 views

Do i need to add index for apply filtering for this query?

Below is my query: with cte as (SELECT a.postId from (SELECT p.postId FROM join_area ja INNER JOIN post p USING (areaId) WHERE ja.belongAreaId = 29 AND p....
-3
votes
1answer
44 views

What actions or practices contribute to high CPU and RAM usage in SQL databases? [closed]

I'm curious about which common practices or actions contribute to spikes or high CPU usage and RAM usage for SQL databases, I am trying to estimate the resources I would allocate to a new MySQL ...
1
vote
1answer
17 views

How to skip prefix column check in multi column index in MySQL?

Below is my demo table structure: CREATE TABLE `table1` ( `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent', `postLocation` smallint(6) NOT NULL, `postArea` smallint(6) ...
1
vote
1answer
37 views

MySQL order of execution

I am running a very long query described below. It fetches the next needed action for each account on an automation system. I.e. SELECT Account.id, (IFNULL(**Should send message query**, ...
0
votes
0answers
21 views

How can i remove filesort for this subqueires?

This is the Query: SELECT A.* FROM messages_thread_user mtu, LATERAL (SELECT m.*, u.userName FROM messages m JOIN ...
0
votes
0answers
29 views

Query Optimization - Potential ill effects of adding an full text index to a frequently updated table

We have a query of the following type which we are trying to optimize. SELECT table_one.* FROM table_one INNER JOIN table_two ON table_two.fk_id = table_one.id WHERE (table_one.h_id = 20626 AND ...
0
votes
0answers
20 views

optimize query with date type field in mysql

I currently have the following query prepared: select sum(amount) as total from incomes where (YEAR(date) = '2019' and MONTH(date) = '07') and incomes.deleted_at is null When ...
1
vote
2answers
40 views

Why is this query sometimes slow, and what can I do to make it faster?

I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example "2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database. ...
0
votes
1answer
20 views

MySQL - Optimize subquery in Select, where and order by

Im running a query like below. SELECT DISTINCT c.`id` , c.`job-cat` , ( SELECT COUNT(*) FROM `jobs` j WHERE j.`mul_category` IN (c.`id`) AND j.`ending` >...
0
votes
0answers
17 views

MySQL view extremely slow despite indexes

I'm having an issue with a view, when I make a query it brings CPU to 100% and it keeps loading. The view is: https://pastebin.com/ZgB7rr91 channel_data_60 table contains about 1,200,000 records: ...
0
votes
0answers
21 views

Why is turning on Event Scheduler Maxing my CPU?

I have search the web for the last week trying to figure this out. I have exported my tables and events, re-installed the lastest MySQL and imported. I have tried checking individual procedures. My ...
0
votes
1answer
35 views

How to link customers and suppliers in a database

Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier ...
1
vote
1answer
37 views

Optimize MySQL query retrieval from audit log table

I am running MySQL InnoDB 5.6. This is my first post, so please bare with me of any mistakes. I have this schema: I have a list of invoices in the invoice table, and if I want to see all the ...
2
votes
0answers
67 views

What's the more efficient way of selecting with multi left joins?

I have 4 tables, the first one is "student", the other 3 tables are different records of the student's activities. All tables have a id column as the primary key. student.id is varchar(64) (similar ...
1
vote
1answer
102 views

Optimize MySQL query and index for multiple column range query

I have a table like this: CREATE TABLE IF NOT EXISTS `jobs` ( `job_id` varchar(36) NOT NULL, `job_status` varchar(30) NOT NULL, `created_at` datetime NOT NULL, `lease_date` datetime, ...
0
votes
0answers
21 views

Optimizing the read of child records of many parents

The main table has a complex query that selects the jobs that are to be displayed on the current workstation. In addition to the main job record there are a couple of child tables with a few dozen ...
1
vote
1answer
49 views

How to automate basic ddl scripts execution and management [closed]

Looking for suggestions (A Script, third party tool, software etc.) which can help automate some of the administration tasks around execution of DDL scripts across environments. Database is usually ...
0
votes
0answers
19 views

Composite index to cover sum of columns

Is it possible to create an index in MySQL that will help with a query like this: SELECT a, b, c FROM `table` WHERE a + b + c > 0 I've tried adding a composite index on all a, b, and c to no ...
0
votes
1answer
25 views

Analyzing a query plan for a query with TWO predicates, but index only has ONE

Note: This is from an introductory database systems course at my university so I'm not entirely sure if it matters, but for what it's worth we are being taught MySQL. One homework question I'm doing ...
1
vote
1answer
26 views

Mysql Query Optimisation For Joining a BETWEEN Date and Date query

I have some instagram API data sitting across two tables. One with post_data, and one with profile_data. The profile_data table has a timestamp of when the account was checked through the API to get ...
0
votes
0answers
26 views

MySQL optimize table

I have a table which record the bandwidth usage in MySQL. Every minute I have a new record. Here is my table : +------------------+--------------+------+-----+---------+-------+ | Field | ...
1
vote
0answers
61 views

Slow ORDER BY with large and small tables

I have one small table (200 rows) and two large tables (200k rows each). Small table has primary key id and some other fields. First big table has id field (not a primary key), which are duplicated, ...
1
vote
1answer
37 views

Optimize MySQL InnoDB query for SUM and Order By

I have an MySQL InnoDB table with 1.3M rows and 670MB size: +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------...
3
votes
1answer
41 views

MariaDB configuration for many updates, writes

Based on few tutorials that I show I created the following MariaDB configuration for my server. Server has 32 GB ram, SSD, 8 cores and is dedicated to run MySQL. The biggest table has about 1,5 ...
1
vote
2answers
69 views

MySQL: need help making a query faster

I wrote a query that finds me records in discovery table that contain a needle from whitelisted (LIKE %needle%) and don't have a record in logs table (and some other easy to understand filtering): ...
0
votes
1answer
43 views

Optimizing MYSQL statements

Recently, I started working on a project, where I came across many MySQL statements like this: SELECT s.Important, c.Interesting FROM table1 s, table2 c WHERE s.IdForTableC = c.id or SELECT q....
0
votes
2answers
35 views

Cannot figure out how to remove this Using Temporary

I have what seems like a simple query, and my indexes seem to be sufficient without the INNER JOIN, but after I add the join, I end up seeing "Using Temporary". Would also appreciate any good ...
1
vote
0answers
41 views

MySQL Query Optimization Group By with Max [closed]

I have posted same question on at stackoverflow.com but I did not get any structural solution so I am trying to get answer on dba specific community. Trying to optimise MySQL query which uses Group ...
1
vote
2answers
75 views

Need suggestions in improving my.cnf configuration

i need some suggestions in improving MySQL performance, i didn't find any issues in general but i feel MySQL is taking more CPU than before. Below is the output from mysqltuner.pl >> ...
0
votes
3answers
138 views

MySQL Single Left Join Very Slow

SELECT contacts.email, emails_stats.id FROM contacts LEFT JOIN emails_stats ON contacts.id = emails_stats.contact_id WHERE contacts.client_code = 121212121212 GROUP BY contacts.email; ...
0
votes
0answers
41 views

Optimize query for orders with unseen comments

I have following MySQL tables in an application I have to support: CREATE TABLE orders ( id int auto_increment primary key, author int(8) not null, manager_id int(10) default 0 ...
-2
votes
1answer
38 views

How would you do this simple query in the most optimized way MYSQL and also MARIADB

pkid clientname location 1 Jones NY 2 Jones NJ 3 Jones TX 4 Jones FL 5 Jones IA 6 Jones NC 7 Jones SC 8 Jones VA 9 Smith FL 10 Smith CO ...
0
votes
2answers
96 views

How does MySQL store data, can I detach a mysql Database?

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to ...
1
vote
1answer
43 views

Advice on fine-tuning query and index for performance

Currently my query is as below: select id_request,username_request from darkhast_follower where darkhast_follower.id_request != '9762952594' AND darkhast_follower.status =0 AND NOT exists (...
0
votes
2answers
161 views

Optimize MySQL update statement

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.1 million rows. I have ...
-1
votes
1answer
60 views

MySQL - Optimize a table which has mostly text data type

I recently faced some performance issue on a CMS based application. I can somehow fix other tables. But Im not able to optimize the table which has 40+ columns and 10 columns are having text as the ...
0
votes
0answers
69 views

Improving/ Optimizing Performance Query in MySQL PHP

I have a invoices, invoices_items, order, order_items. Invoices and Orders tables contains around 1 Millions records. Invoices_items and Orders_items tables contains more than 2 Millions records. ...
0
votes
0answers
39 views

add index and change int data type to bigint the fastest way

How do I do this without downtime, or at least minimal downtime (less than 3 hours)? Is there any way to tweak my.cnf to at least make this process of indexing and change data type faster? Below is ...
0
votes
0answers
43 views

Left join with same table making query too slow — MySQL

We have a table server_user_disk_usage which tracks the disk usage of users from different servers. Query is to find sum of last disk usage of each user from different servers SELECT m1.user_name, ...