All Questions
50 questions
1
vote
1
answer
614
views
Why is the execution time on Datagrip longer on MySQL 8 than MySQL 5.7 on a new Linux Server?
I am migrating from a 4 year old setup to a brand new high performance server and am experiencing slower performance than on the old machine.
The old setup is a Ubuntu 16.04 Server on bare metal Intel ...
- 313
0
votes
1
answer
56
views
Slow SQL taking almost 1s to execute with only 24000 records! (EXPLAIN included)
Any ideas how to improve and what the actual culprit is?
records in product are only 24000
takes 0.85s to run
Mysql 5.7
select `product`.* from `product`
inner join `manufacturer` on `product`.`...
- 211
2
votes
1
answer
161
views
MySQL performance issues with occasional complete freezes
we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes ...
- 121
0
votes
1
answer
273
views
Tuning query - reduce temporary and/or filesort use with join
My query runs in 60-90 seconds, I would like to see how can I improve it.
One table contain partitions and have tens of million rows each partition.
The other table contain 5-8 million rows between ...
- 533
1
vote
2
answers
4k
views
How to speed up foreign key creation in MySQL for large tables?
I am trying to set a foreign key constraint on a 5.7 InnoDB table with 30M+ rows.
It now already runs for over 1h on a quad core 64GB server. The processlist outputs the state copy to tmp table for ...
- 313
1
vote
1
answer
2k
views
MySQL boolean field search vs indexed varchar performance
Will a select filtering on a boolean field be faster than a select filtering on an indexed varchar(255) field?
Specifically:
MySQL 5.7
In this case, records of a certain table should never be deleted. ...
- 113
1
vote
2
answers
819
views
Configure MySQL to minimize file locks
*** EDIT ***
8GB RAM
SHOW VARIABLES: https://pastebin.com/ANAiq1E2
SHOW GLOBAL STATUS: https://pastebin.com/pwWKRiie
*** END EDIT ***
To start off, I'm not a DBA, so I may have overlooked something ...
- 111
0
votes
1
answer
934
views
MySQL - Force index degraded performance over time
Last year we migrated data from our old product environment into a new environment with our new product.
Since the new product was still fresh, we found that some of the new queries were very slow ...
- 365
0
votes
3
answers
144
views
Wrong index been peeked
I have an index that filters 99% of the table i.e. ix_magic_composite (for that query arguments).
When I add another or filter it chooses the wrong index, i.e. fTS even if I create an index that ...
- 533
0
votes
1
answer
3k
views
Why doesn't MySQL use indexes for subqueries?
This query takes forever to run (30+m - infinity).
select date,
sc,
( select count(fingerprint_id)
from stats
where hit_date >= t.date
and hit_date ...
- 226
2
votes
0
answers
796
views
MySQL performance degradation with heavy inserts
I have an application that does heavy inserts into 5.7 MySQL database. The performance is really good for normal workloads, however, when there's an intense activity in the database the queries (...
5
votes
4
answers
8k
views
MySQL DELETE statement doesn't use index although the same SELECT query does
I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following:
id,
cookie_id VARCHAR(45),
country ...
- 51
1
vote
0
answers
520
views
Slow MySql with left join and Group by
Below is the Mysql 5.7 Table query which has a left join with the group by. When I execute it takes too much time.
line_items has around 3.5M data and users have 1000 rows.
Below is an index of my ...
- 135
1
vote
1
answer
587
views
MySQL Database on RDS with memory issues
I am facing high memory utilization issue on RDS MySQL server. We are having other issues too, can someone give me a help?
My database has 120GB, including indexes.
Can anyone help me out to ...
1
vote
1
answer
7k
views
MySQL Inner Join too slow
My machine disk is SSD and memory is 32GB.
My executed SQL:
SELECT `country_detail`.`country_name` AS `country_name`
FROM `statistic_detail`
INNER JOIN `country_detail` ON (`statistic_detail`.`...
1
vote
2
answers
2k
views
MySQL swaps on system with more than enough RAM
Om my new dual AMD EPYC 7401 24-Core (128G RAM) MySQL starts swapping even after a restart with no data in memory.
NUMA
# numactl --hardware
available: 8 nodes (0-7)
node 0 cpus: 0 1 2 3 4 5 48 49 ...
- 193
1
vote
1
answer
48
views
selecting data after filtering out
I am using RDBMS MySQL 5.7. I have 3 tables with the following structures:
Table A - primary table - products:
+----+-----------+-------------+------------+--------+
| id | title | description |...
- 65
1
vote
1
answer
44
views
Why does performing a query on a second environment, it takes forever to run, and scans hundreds of millions of rows?
I have a query, it is fairly big, but not THAT big. One the baseline environment, it runs in less than 1/3 of a second. When we moved the database to RDS in Amazon, it takes 10-15 minutes to run.
I ...
- 125
0
votes
0
answers
108
views
Mysql memory_global_total is smaller than memory usage in top [duplicate]
When I try top in my ubuntu the result
top - 16:03:09 up 16 days, 21:24, 2 users, load average: 3.53, 2.72, 3.46
Tasks: 395 total, 1 running, 282 sleeping, 0 stopped, 0 zombie
%Cpu(s): 10.0 ...
- 31
0
votes
1
answer
78
views
Why the difference in query time for these two queries?
I have a MySQL 5.7 database with a large table that has about 70,000,000 rows. There are 4 columns: Id, Time, Value, Monitor_Id. Id is the primary key, Time is indexed and Monitor_Id is a foreign key. ...
- 103
2
votes
2
answers
168
views
How to optimize a MySQL joining to itself?
I need to optimize a MySQL (5.7.12) query. I've been reading the docs about optimization in MySQL docs but It's been hard for me to comprehend it. I have two tables:
vehicles
CREATE TABLE `vehicles` ...
- 248
0
votes
1
answer
181
views
Very slow JOIN + ORDER BY query only in dev environment
I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query.
This query is taking 75 ...
- 123
1
vote
1
answer
33
views
Is it possible to change between SELECT column based on an "IF" statement? and use INNER JOIN variables?
I have a table of 'mapped_products':
product_id | price
A table of 'product_id_to_name':
product_id | product_name
(please don't ask me why, this is an already built web-app i am working on)...
- 111
0
votes
1
answer
91
views
Why a certain field causes a slow query?
I have 6 almost identical queries on a page. I am loading logs by keyname.
Lattitude and longitude take over 4s each, where as 4 other values take 0.09s How could just the one text value in where ...
- 103
2
votes
2
answers
1k
views
When does MySQL begin to start sending data to a client?
I am working on MySQL 5.7 with the default configuration of InnoDB storage engine. I have two questions each for a different scenario and need to know the internal working on how MySQL sends data to ...
- 155
-1
votes
2
answers
758
views
MySql Not Logging Slow Queries
I've set up slow query logging for MySQL 5.7:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query_log.log
long_query_time = 10
log_queries_not_using_indexes = 0
Then to test ...
- 385
1
vote
2
answers
389
views
Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 "Using index condition"
My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).
EXPLAIN SELECT
*
FROM
`...
- 385
0
votes
2
answers
291
views
No 'Copying to tmp table' state in profiling a mysql query
I have a table with 100k rows
CREATE TABLE `small_table` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`varc` varchar(255) DEFAULT NULL,
`txt` text,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB ...
- 674
0
votes
2
answers
3k
views
mySQL query optimizer not using indexes
In our production environment (mySQL 5.6.40 on Amazon RDS), for certain queries the query optimizer ignores table indexes and takes several minutes to complete, whereas in our non-RDS test environment ...
2
votes
2
answers
3k
views
MySQL Performance vs. Memory
We are running MySQL Ver 5.7.18 on an Amazon Lightsail Linux instance (2GHz Single Core CPU, 2GB RAM, 40GB SSD).
Besides MySQL, we also run some Java services on this machine, which access the ...
- 123
4
votes
1
answer
477
views
Will increase in Innodb_buffer_pool_size increases insert query performance?
We have 3 identical database boxes(M1<->M2->S1) with Mysql 5.7.17 installed and having memory 96GB RAM. In these boxes nearly 42GB occupied by Index and data length of Innodb Tables and 38G ...
- 41
0
votes
2
answers
936
views
Worse performance after create empty table
I'm working with MySQL 5.7.10 and I have this issue.
I have this table to trace requests:
CREATE TABLE `invoice_requests` (
`REQUEST_ID` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_spanish_ci',...
- 153
1
vote
3
answers
1k
views
Many-to-many tag filter query with a junction table get slower with more tags. Can it be faster?
Part 1
I have this query to find some videos containing all given tags. Here with tags 1,9,27,13,67 as an example.
SELECT *
FROM video v
WHERE v.id IN (
SELECT tr1.vid as video_id FROM (SELECT ...
- 187
-1
votes
1
answer
4k
views
Replace Subquery with JOIN - MYSQL
Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query :
SELECT sa.id AS patAppIdSA,
sa.rte_id AS patRTEId,
sa.case_type_id AS ...
- 109
6
votes
2
answers
33k
views
Is the MySQL JSON data type bad for performance for data retrieval?
Let's say I have a MySQL JSON data type called custom_properties for a media table:
An example of the json data stored in the custom_properties column could be:
{
"company_id": 1,
"uploaded_by": "...
- 161
0
votes
1
answer
303
views
Optimal Hardware for > 30K Requests / min? [closed]
We have some performance issues with one of our Test-Machines a MySQL 5.7.21 Standalone Server.
We have: 8 CPUs 16GB RAM (buffer_pool_size 5.6 G) and need to handle > 30 K Requests / minute.
When ...
- 23
2
votes
2
answers
53
views
Is there benefit from having 2 tables for 1 entity?
I'm using mysql 5.7
I'm making app that has 2 activities: list of entities and viewing single entity (full).
The full entity has 7 related entities and 100 columns.
On the list activity I need only ...
- 23
0
votes
2
answers
912
views
Multiple Schemas for increasing MySQL (5.7) Performance
Lets start with my question first:
Does having multiple schemas (1 main and 1 daily - having 5-6 tables with same structure as main, but only working for the day and will be synced back with the main ...
0
votes
1
answer
169
views
MySQL Read lock and query performance analyses
I've a table with the following structure.
CREATE TABLE `wallet_details` (
`id` varchar(32) NOT NULL,
`status` varchar(16) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_status` (`status`(3))
) ...
- 101
1
vote
1
answer
139
views
high concurency issue with read-write on mysql table
I have a mysql innoDB table (mysql 5.7, ubuntu 14.04) :
+-------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra ...
- 85
8
votes
2
answers
10k
views
MySQL commits taking time
We are facing an issue, where at certain times there are number of commits that takes a more than a second. The time interval for this is not fixed. Our application sends thousands of commits but at ...
- 195
3
votes
2
answers
2k
views
Very Slow Query
Problem
Below is a query I put together in MySQL Workbench that I thought would be relatively simple. But when running it, on a larger data set (7000+ rows), I find it taking over 200 seconds, uses ...
- 224
1
vote
2
answers
8k
views
Changed limits: max_connections: 214
I am using MySQL version 5.7.15 , ubuntu0.16.04.1. When I start the MySQL, I am getting below log,
2016-11-06T13:44:56.838233Z 0 [Warning] option 'table_open_cache': unsigned value 33554432 adjusted ...
- 113
1
vote
3
answers
3k
views
Search is slow in a partitioned table
I'm running MySQL 5.7.15 on Ubuntu server 16.04.
After I've partitioned a table by range, select queries for some values are slow. I've checked that queries are slow if key is missing in older ...
0
votes
1
answer
800
views
MySQL insert with sub select slow
We are using Python and LOAD DATA INFILE to load data from CSVs into our staging database. From staging we have sql scripts moving the data into our actual production database.
LOAD DATA INFILE is ...
- 101
5
votes
1
answer
15k
views
Subquery inside a select vs left join
Often I have to return the count of of some row in a query that fetch other non related rows.
For example A table User a table Review and a Table picture
User:
id
nickname
Review:
id
to_user_id
...
- 289
-1
votes
1
answer
94
views
Server settings with 36GB handling app+data
I'm setting up a server to handle a multi-site configuration on a beta application for a partner in a new business venture.
Trying to assess sane values for MySQL.
Currently I have set:
...
- 1
1
vote
1
answer
950
views
Slower running Query with better Filter?
mysql> SELECT *
FROM history_uint h
WHERE h.itemid='1825689'
ORDER BY h.clock DESC
LIMIT 2 OFFSET 0 ;
Empty set (0.00 sec)
Explain:
id: 1
...
- 227
2
votes
1
answer
820
views
MySQL best performance settings [closed]
I am going to do some performance tests on MySQL server as I want to evaluate how MySQL behaves in some situations. MySQL is the only (additional) software installed on my server (Windows Server 2012),...
- 21
3
votes
2
answers
3k
views
Release MySQL Memory and Performance Tunning
I am new to the MySQL database. I have recently installed MySQL 5.7 and configured the my.ini file. Please find the below copy of the file.
The server is a dedicated server for MySQL database and the ...
- 31