All Questions
Tagged with mysql optimization
724 questions
0
votes
0
answers
52
views
How can I optimize my table better with 130 million rows of data?
I have a consumers table with name, email, identity number, contact and address. There are columns such as unit, street, postcode and more but not used. All the columns has been indexed and address is ...
1
vote
1
answer
49
views
Why MySQL can't opimize a query with a LEFT JOIN and a WHERE clause with an OR condition?
I have 2 different queries, each of them runs very fast (within milliseconds).
Query 1:
select t1.id
from table1 t1
left join table2 t2 on t1.id = t2.t1_id
where (t1.update_date >= :...
0
votes
0
answers
44
views
Indexing and optimize large query with multiple joins
I have search query with multiple table join as
SELECT
DISTINCT pp.person_profile_id,
pp.middle_name,
pp.prefix,
pp.first_name,
pp.last_name,
pp.suffix,
pp.privacy_type,
...
0
votes
0
answers
57
views
Issue with query performance coming from mysql to mariadb
we were in the process to migrate from Mysql to MariaDB, but we've encountered a performance issue with several of our queries.
The same (already simplified) query
SELECT
count(*)
FROM
vorgang,...
0
votes
2
answers
38
views
Should I create a separate table for non null user_id values in my MySQL database schema for performance?
I have a MySQL database schema for storing uploads, where each configuration can be associated with a user account (user_id) or uploaded as a guest without an associated user account (null user_id). ...
3
votes
1
answer
57
views
fetching data from 2 10M+ rows tables
I need to run the following query on my production database, using 2 tables that contain more than 10M rows each :
SELECT rm.parent_id as processed_media_id
FROM render_medias rm
JOIN processed_medias ...
0
votes
3
answers
127
views
How to best optimize computer for intensive Mysql queries?
I have a series of tasks I run in MYSQL 8 every day. Actually these tasks are run from a vb.net application that interfaces with MYSQL using 8 connections at a time. These tasks are a series of ...
2
votes
1
answer
91
views
How to optimize following subnet that is decomposed into multiple sub-one?
I have a table which like like that:
Id
IpRange
Mask
Owner
1
127.0.0.0
24
1
2
127.0.1.0
24
1
3
127.0.2.0
24
1
4
127.0.3.0
24
3
I'm checking if an IP is in the range thanks to this :
(inet_aton('...
1
vote
2
answers
52
views
Table partitioning for data pruning purposes
We are using MySQL database. The total space it can accommodate is 250 GB. It is currently at 225 GB. With this, there had been this initiative within our team to create a daily cron job for daily ...
1
vote
2
answers
62
views
MySQL optimize query against table containing 13M records
I recently started working with a legacy Rails application. Trying to go through with some of the queries used to get analytics related data. Some table contains around 13M records. Running count ...
1
vote
1
answer
84
views
MySQL - Slow Performing Subquery when DISTINCT Is Used
I have this query that runs very slowly when DISTINCT is used;
SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
1
vote
1
answer
262
views
Wrong execution plan in MySQL 8.0 when using JOIN in WHERE NOT IN clause
After a migration from MySQL 5.7 to MySQL 8.0.34, we have a very strange behavior with a query when semijoin is on in the optimizer_switch.
1. The problematic query:
SELECT COUNT(s0_.id)
FROM ...
0
votes
2
answers
91
views
How to choose index to optimize this MySQL query?
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' AND symbol = 'ZROZ' AND blacklisted = 0
ORDER BY high DESC LIMIT 1;
SELECT * FROM ehd_historical_data
WHERE exchange = 'NYSE ARCA' ...
1
vote
1
answer
63
views
Finding the latest status
I have 2 tables events and event_status.
The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping.
I need a optimized query ...
0
votes
1
answer
128
views
Help with SQL Query: Is there a cost-efficient way to join multiple tables to one main table?
I have a main table set up like this:
id
table_to_join
table_item_id
1
products
123
2
products
577
3
products
344
4
products
1230
5
images
14
6
images
42
7
video
555
8
products
400
9
video
9
...
1
vote
1
answer
42
views
Slower MySQL queries involving both wp and custom table
Below are less efficient MySQL queries involving wp posts, taxonomies, and a custom movie table search. The custom movies table contains around 30k rows, and wp posts have been generated with the ...
0
votes
2
answers
48
views
Optimizie MySQL query: col_name IN (val1, ..., valN)
I had posted a question about mysql equality range optimization here.
At this post, I want to discuss about why mysql optimizer is too bad for this type of query: col_name IN (val1, ..., valN).
My ...
2
votes
3
answers
421
views
How to manage mysql database table index
I am not aware of how to manage an mysql database table index and I attempted to implement it, but I am uncertain about managing the index for this extensive MySQL database table (Will do it at my end ...
0
votes
1
answer
61
views
MySQL gets slow before optimizing table
We have a custom made webshop built on the Laravel PHP framework which processes, on a busy day, 7000 orders a day. Since all orders are sent to a third-party point of sale system via an API, the ...
0
votes
1
answer
77
views
What strategy should I use for my gigant MYSQL Tables?
I'm working in a system that is using MySQL as primary DB.
One of the biggest tables is almost 1TB in size, others are around 100-300 GBs.
Queries started to be really slow.
I search some info, and I ...
1
vote
2
answers
56
views
How to optimize this query or these many indexes?
What am I doing wrong here?
I will post the table structure and indexes first, then the query that is slow, and many others that are very fast (instant).
How to optimize that one query to run faster ?
...
1
vote
1
answer
42
views
Could cloning / imaging SSD cause MySQL data to be more fragmented?
On a MySQL server (InnoDB tables) with /var/lib/mysql around 350 GB, I cloned the nvme SSD (using Macrium Reflect) in order to compare query speeds with a faster SSD. But queries are around 4 times ...
2
votes
2
answers
3k
views
MySQL db optimization error on innodb tables
I have a database named 'mysql' which is being used on RHEL 8 server running MySQL 8.0.32.
When trying to optimize the entire database using the mysqlcheck command 'mysqlcheck -o mysql' the below ...
0
votes
0
answers
80
views
MySQL occasional performance issues
I am currently working on system with MySQL DB and SpringBoot (Hibernate). Both are located on the same server. All tables use InnoDB. Although there are no slow queries (5+ seconds), there is ...
0
votes
1
answer
122
views
How to tell if a MySQL query speed is bottlenecked by storage or CPU speed?
Using MySQL 8.0.30 on Rocky Linux 9
For slow MySQL queries in general, not for a speciffic one, is there a way to tell if query speed was bottlenecked by storage speed, cpu speed, or maybe even ram ...
0
votes
0
answers
163
views
Better way to store stock levels for products?
I currently have 3 tables: locations, products, and stock, which are pretty self-explanatory:
The fields of stock are location_id, product_id, and quantity, which is great... until I add a new ...
2
votes
1
answer
141
views
LEFT JOIN not using index when `column1` < `value` AND `column2` > value
I'm trying to LEFT JOIN a table to another table where a column value is between two column values in the second table, and it is not using an index when doing this seemingly simple query. It has ...
1
vote
2
answers
1k
views
Very slow DROP INDEX, ADD PRIMARY KEY index
This is probably related to another question I had, but not sure.
So my table url_meta is 25 GB including data and indexes, 70 million records.
Data 18.3 GiB
Index 7.3 GiB
Overhead 5.0 ...
1
vote
1
answer
675
views
Optimizing 2x 1.8GB tables takes 50 minutes
I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 ...
1
vote
1
answer
558
views
effectiveness of creating index for ORDER BY column when used with many WHERE clauses
Say I am trying to build a pagination for a simple e-commerce app where user can search, filter, and sort items, and the result is displayed in an infinite scroll UI. I'm planning to use the cursor ...
2
votes
1
answer
194
views
MYSQL query slow on sorting
I came across an issue, i have the following query:
SELECT
DISTINCT
p.id,
p.title,
p.description
FROM
(
SELECT
`post_id`,
`s_type`
FROM
`search`
WHERE ...
0
votes
1
answer
120
views
optimization with subquery not working as expected
I have a MySQL InnoDB database. One table called affymetrixProbeset contains more than 300 million rows.
Querying this table with INNER JOIN to other tables, with an ORDER BY and offset/limit takes ...
0
votes
0
answers
52
views
What's the best/fastest way to divide a database into smaller one (MySQL)
I have a single database with all my clients and I need to divide it into multiple "little" databases each with one client for post-treatment purposes.
(I have a "clients" table ...
1
vote
1
answer
341
views
Force Index does not work on aws mysql
I have a table sales with a compound index on (user_id, eastern_date, state)
CREATE TABLE sales (
id int not null auto_increment,
user_id int not null,
eastern_date date not null,
...
2
votes
1
answer
1k
views
Is it faster to have one stored procedure with multiple if checks before one insert, or to split it out into multiple stored procedures
I am trying to optimize the inserting of some data into our database, but I'm not too sure if this stored procedure would be more efficient if it was split out into multiple stored procedures.
I have ...
0
votes
1
answer
290
views
MySQL - Optimize select with join to a large table
I have done some digging and found exactly no help at all for questions similar to this one. Basically I have two tables where one stores user posts and the other holds the moderation of the records ...
2
votes
1
answer
737
views
Imporve MySQL InnoDB Full-text Search Performance
1.Background:
DB: MySQL 8.0, InnoDB engine;
Table size: About 2M rows of 2G data; FULL_TEXT index column: sentence (TEXT data type) https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
Old ...
1
vote
2
answers
176
views
Could MySQL perform better on a 10GB network storage with raid vs local HDD?
Compared to using a HDD on local computer and do many MySQL read / writes on it, could it have more queries per second if I install 10GBe network cards and I store the MySQL data on a raid network ...
1
vote
1
answer
84
views
mysql optimization for intensive schema-altering queries (DDL)
In the context of CI would like to massively parallelise the running of tests. The code under testing require a database to be present, so MySQL is used (as the application goes with that right now).
...
1
vote
1
answer
719
views
Simple query with a single join very slow
I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
0
votes
2
answers
125
views
Fastest query to process data in small batches without repetition
I have java app that is using MySQL in the backend. I have the following table:
A = int, B = varchar, C = timestamp
A | B | C
1 | 100 | 2022-03-01 12:00:00
2 | 200 | 2022-03-01 12:00:01
3 | 100 | ...
3
votes
1
answer
409
views
How to optimize simple table with groups and timestamps (SUM over 15M rows with GROUP BY)
I think I have quite simple question, but I couldn't find the answer anywhere.
I'm using MySQL and I have a simple table:
id
timestamp
groupId
costA
costB
costC
...
1
2022-02-01 19:45
1
5,13
3,20
30,...
4
votes
2
answers
1k
views
Should JSON columns be separate from a wide table in MySQL?
JSON is stored similar to LONGTEXT datatypes. The MySQL docs advise for TEXT datatypes:
If a table contains string columns such as name and address, but many queries do not retrieve those columns, ...
3
votes
1
answer
1k
views
Making an index a unique index in very large MySQL table within one transaction - is the following approach safe?
For the purpose of optimizing SELECT statements I am trying to make an index UNIQUE with the following SQL statement in MySQL:
ALTER TABLE credentials DROP INDEX special_credential_id, ADD UNIQUE KEY ...
0
votes
2
answers
268
views
How to optimize a big table for backup
I have a table that is not too much big but still, it takes 30GB of storage.
When I try to make a backup with mysqldump it takes a lot of time and resources and I want to optimize it but I don't know ...
0
votes
2
answers
65
views
MySQL - Planning Cost Higher Than Number of Blocks Occupied in Disk
There is a table dept and it has 100 rows, all nicely tucked in 1 block, as seen in the mysql.innodb_index_stats table.
table_name
index_name
stat_name
stat_value
sample_size
stat_description
dept
...
0
votes
2
answers
857
views
MySQL multiple subqueries - slow ordering
Any idea how to optimise this query?
It runs very slow when using the ORDER BY (~10 seconds) clause. If I remove the order_by clause, then it executes for milliseconds.
Problem is, that I need to been ...
2
votes
2
answers
2k
views
Nested loop on primary key
CREATE TABLE `request` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`created_by` int(11) unsigned DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
...
0
votes
1
answer
2k
views
MySQL not using index as expected with a WHERE IN clause
I have a table called mytable and I'm trying to optimize a query on it. I generated the index ind5 on it, and it has the nullable int column col_1 in first place. This query has a WHERE IN clause, ...
0
votes
1
answer
1k
views
SQL query working with 8.0 but not working with 5.7 [closed]
I have a mysql table like this
CREATE TABLE `users_search_activity` (
`ID` bigint(20) UNSIGNED NOT NULL,
`user_id` int(11) NOT NULL,
`country_id` int(11) NOT NULL,
`search_keywords` text ...