Skip to main content

All Questions

Tagged with
Filter by
Sorted by
Tagged with
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 ...
Jon Lam's user avatar
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 >= :...
andrescmasmas's user avatar
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, ...
Nagesh Katke's user avatar
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,...
csc's user avatar
  • 9
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). ...
user3075373's user avatar
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 ...
VaN's user avatar
  • 131
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 ...
Luke Krell's user avatar
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('...
Elikill58's user avatar
  • 181
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 ...
Xmus Jackson Flaxon Waxon's user avatar
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 ...
Aparichith's user avatar
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 ...
Marisa's user avatar
  • 23
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 ...
Arie S.'s user avatar
  • 13
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' ...
user avatar
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 ...
Anwesh Budhathoki's user avatar
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 ...
peppy's user avatar
  • 33
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 ...
Rahul K's user avatar
  • 141
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 ...
kietheros's user avatar
  • 145
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 ...
Rahul K's user avatar
  • 141
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 ...
Rogier's user avatar
  • 1
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 ...
Danilo Bassi's user avatar
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 ? ...
adrianTNT's user avatar
  • 204
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 ...
adrianTNT's user avatar
  • 204
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 ...
PirateRagnaros's user avatar
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 ...
Ivan's user avatar
  • 1
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 ...
adrianTNT's user avatar
  • 204
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 ...
Basil's user avatar
  • 131
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 ...
Marshall C's user avatar
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 ...
adrianTNT's user avatar
  • 204
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 ...
adrianTNT's user avatar
  • 204
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 ...
hskris's user avatar
  • 111
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 ...
alexfsk's user avatar
  • 21
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 ...
jwollbrett's user avatar
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 ...
Bahaldrish's user avatar
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, ...
RSHAP's user avatar
  • 113
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 ...
impo's user avatar
  • 121
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 ...
Dennisrec's user avatar
  • 101
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 ...
123mig's user avatar
  • 21
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 ...
adrianTNT's user avatar
  • 204
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). ...
BogdanSorlea's user avatar
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 ...
flyingdutchman's user avatar
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 | ...
Bojan Vukasovic's user avatar
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,...
atay's user avatar
  • 31
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, ...
EliteRaceElephant's user avatar
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 ...
Blackbam's user avatar
  • 225
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 ...
Ali Akbar Azizi's user avatar
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 ...
Arun S R's user avatar
  • 207
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 ...
Milen Mihalev's user avatar
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; ...
Thomas Corbisier's user avatar
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, ...
Johnny's user avatar
  • 150
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 ...
newuser's user avatar
  • 681

1
2 3 4 5
15