207 questions
0
votes
1
answer
113
views
Is there any other approach to update or chunk to populate a new column with millions rows? [closed]
My query is taking too long to run when I use general filters, but it's fine with specific filters. I suspect it's an optimization issue.
To fix it, I'm trying to create a non-clustered index on a new ...
- 13
0
votes
0
answers
68
views
Optimizing MySQL LIKE Query with Pattern Matching for Large Data(20M+ records) and FULL TEXT SEARCH not giving accurate results
Optimizing MySQL LIKE Query with Pattern Matching for Large Dataset (20M+ records)
I'm struggling with performance issues in MySQL while searching through a large table containing over 20 million ...
- 11
1
vote
1
answer
42
views
How to optimize query performance in GridDB for large time-series datasets?
I’m working on a project that involves managing large time-series datasets in GridDB. Currently, I’m experiencing significant query latency issues as the dataset size grows. While GridDB performs well ...
- 21
1
vote
1
answer
120
views
What impact has uniqueness constraint jsonb on postgres db performance
I'm designing a postgres database in which there is a table that has a jsonb type column. I would like this column to be unique. There is no need to have two objects with the exact same json ...
- 1,168
-1
votes
2
answers
56
views
Does replacing string column with repeating values at some point with an int FK have any performance benefits in RDBMS? [duplicate]
In order to store the country information for a person I did:
CREATE TABLE test
(
id INT IDENTITY(1, 1),
name VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
...
- 669
-1
votes
1
answer
401
views
Can modern SQL syntax be translated into a relational algebra tree?
I have a parsed representation of an SQL query in the form of an abstract syntax tree.
I'd like to convert it into an intermediate logical tree representation, which can in turn be expanded into ...
- 1,054
-1
votes
1
answer
50
views
How do I optimize my query for a table with filters on two columns that has a one-to-many relationship
Using Prisma and PostgreSQL:
model Chat {
id Int @id @default(autoincrement())
Users UserChat[] @relation(fields: [], references: [])
Keywords ChatKeyword[]
chatName ...
0
votes
0
answers
98
views
Optimal way of creating a large table from a SELECT statement
I've got a pretty complex select query, but it's otherwise optimized based on the EXPLAIN. I want to create a concrete table from the results of this select. But the challenge is that this is a large ...
- 2,395
0
votes
0
answers
438
views
SQL Script looking for missing indexes is showing some existing indexes
I have the following SQL script to use for our Microsoft Dynamics NAV Database (MS SQL Server). It looks for missing indexes in the database. We have the problem that the script also finds some tables ...
1
vote
0
answers
30
views
Can we partition a table based on a timestamp column in GridDB?
In MySQL, it is possible to partition a table based on a timestamp column using the PARTITION BY RANGE clause in the CREATE TABLE statement. Can a similar approach be used in GridDB to partition a ...
- 69
0
votes
1
answer
218
views
Adequate index type for a unique id
The situation I am having is the following. I have a transactions database table. I have both an incremental id for each transaction as well as a unique ID to use for front-end purposes. An example ID ...
- 2,663
0
votes
0
answers
346
views
How to avoid PL/pgSQL loops? What are performance alternatives?
I have close to 200M records in my tables. My migration script runs very slowly (for hours).
---My OLD Tables---
orders_old table 30M row count
id
createdDate
status
price
1000376453
2021-10-14
...
- 706
1
vote
1
answer
94
views
Enforce JOIN instead of Subquey when using "__in" filter in Django ORM
Task: I want to get all invoices where the project title matches a given string. The Invoice has a foreign key to Project.
The problem: I want to use a function for doing the project search so I can ...
- 23.6k
1
vote
0
answers
551
views
Optimizing a wordpress + woocomm store with 100k+ products
So i'm in the middle of an issue right now where the page loading speed is not up to par to what we're looking for and i'm honestly out of ideas for now and i hoped maybe someone smarter can lead me ...
- 31
0
votes
1
answer
67
views
How to filter inconsistent records in sqlite
Say I have SQLite table with the following records:
recID
productID
productName
1
1
Product A
2
2
Product B
3
2
Product C
4
3
Product D
5
3
Product D
recID = primary key, auto increment.
If I run:
...
- 169
5
votes
1
answer
4k
views
Move rows older that x days to archive table or partition table in Postgres 11
I would like to speed up the queries on my big table that contains lots of old data.
I have a table named post that has the date column created_at. The table has over ~31 million rows and ~30 million ...
- 81
1
vote
1
answer
4k
views
Issue with WooCommerce too big database size especially post_meta
I have a Woocommerce site with around 10K products but got a 5.1 GiB database size and post_meta only occupy 4.5 GiB but the wp_posts table is 350 MB only.
I have tried the following query but still ...
-1
votes
2
answers
102
views
MYSQL who are my JOINS appear to make a query faster?
I am trying to improve the performance of a query using a "materialized view" to optimize away joins. The first query below is the original, which employs joins. The second is the query ...
- 169
0
votes
1
answer
253
views
Does mysql optimize result table to avoid duplicate values
In case of a JOIN, especially in a one to many relationship, the result set will very often contain a lot of duplicate information in the result.
For example,
TABLE_A_ID
TABLE_A_FIELD_ONE
...
- 3,610
4
votes
3
answers
6k
views
Is there any way to list all the views related to a table in the existing postgres schema
I got a Postgres database with multiple schemas. I'm trying to optimise my database tables with optimal data types. more often I end with the error
cannot alter the type of a column used by a view
...
- 43
1
vote
1
answer
138
views
Very different query plans for almost identical queries
I am using PostgreSQL 10 + pg_trgm extension.
Table layout:
Column | Type | Collation | Nullable | Default | Storage |
--------------+-------------------+-----------+...
- 55
1
vote
1
answer
3k
views
Postgres improve large update performance?
I'm migrating my Postgres database and am attempting to update a string value to a numeric value, like this:
UPDATE table SET column = 1 WHERE LENGTH(column) = 1;
This table contains around 20 ...
- 621
1
vote
1
answer
151
views
optimisation of multiple left joined tables for fulltextsearch over multiple columns with offset, limit and count for pagination
I currently have four tables that need to be joined with a left join.
There are about 400_000 (400 thousand) of data records (which will grow in the future).
In addition to the left join, the ...
- 45
0
votes
1
answer
465
views
DB2 to SQL Server migration: The conversion of cursors in return statements is not supported
We are converting DB2 procs over to SQL Server using the Microsoft SQL server migration assistant, and getting below error in the generated SQL Server proc:
Errors:DB22SS0245 The conversion of ...
- 86
0
votes
1
answer
43
views
MySql Delete Optimisation
Here is the query which I am trying to execute
DELETE FROM testmachine WHERE workdone != 0 AND timetaken < 1617215400 LIMIT 1000;
It is taking more than 50 sec to execute the Query. I want to ...
- 16
1
vote
1
answer
2k
views
MariaDB optimization for Woocommerce store with more than 55k articles on sale soon
and I appreciate in advance for your help on this. I have a VPS with the following specs:
OS: Centos 7.x
CPU Model: Common KVM processor
CPU Details: 6 Core(2200 MHz)
Distro Name: CentOS Linux ...
- 23
-2
votes
1
answer
632
views
Very slow queries in a large database even with indexes
I have a large database with login and level columns. All queries were slow before I created indexes on these columns:
CREATE INDEX users_login_index on users(login);
CREATE INDEX users_level_index on ...
- 453
0
votes
1
answer
204
views
PostgreSQL function return multiple distinct types
is it possible for a function to return multiple distinct types?
Example:
I have following tables: games, games_rounds and games_players
Now to load a full game by it's id i need to first load it from ...
- 3
5
votes
1
answer
7k
views
Page numbers & total result count in a cursor based pagination
Though it may sound like a stupid question, sometimes it is necessary to show page numbers (and also the last page). What is the best way to calculate total row counts and also calculate page numbers (...
- 914
0
votes
3
answers
1k
views
Millions of rows table optimization SQL Server
I have a table with millions of rows:
CREATE TABLE [dbo].[RequestIdentities]
(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UniqueKey] [nvarchar](256) NULL,
[Timestamp] [datetime] NULL,
...
- 95
0
votes
2
answers
72
views
Which is preferred for mapping values: reusing mapping functions OR building reference tables to do lookups?
Which is preferred for mapping values: reusing mapping functions OR building reference tables to do lookups?
This is a very general high-level question, which, I believe is mostly language-independent....
- 517
-1
votes
3
answers
115
views
What algorithms could i implement to improve the general design and performance of a database?
I'm working on a project for university. Do you guys know what kind of algorithms i could implement that would help with the proper design and general performance of a database? Until now i came up ...
- 67
0
votes
1
answer
84
views
Optimising query with large WHERE IN and Date clause
I have a query similar to:
SELECT
ANY_VALUE(name) AS `name`,
100 * SUM(score) / SUM(sum(score)) OVER (PARTITION BY date(scores.created_at)) AS `average_score`,
ANY_VALUE(DATE_FORMAT(...
- 851
0
votes
1
answer
912
views
MYSQL Select query stuck in "Sending data"
I have an select query with about 1M records, I'm working on Magento 1.9 database.
SELECT IF(sup_ap.is_percent = 1, TRUNCATE(mt.value + (mt.value * sup_ap.pricing_value / 100), 4),
mt....
- 2,840
0
votes
1
answer
443
views
Should I still truncate a postgresql database if I am truncating and refilling the tables instead of deleting/inserting/upserting?
The production database at my company is running significantly slower than the test database (local ~5ms, test ~18ms, production ~1-2 sec). We've been trying to look into why and will be doing some ...
- 45
1
vote
1
answer
112
views
MYSQL view is very slow
I have created view by joining multiple tables to avoid loading time on dynamic query. but its giving the same result by taking more time.
My system configuration is : 8GB RAM i5 Cpu and running ...
0
votes
1
answer
501
views
How to optimize a MySQL query which is taking 10 sec to fetch results
I have a MySQL database. I have a table in it which has around 200000 rows.
I am querying through this table to fetch the latest data.Query
select *
from `db`.`Data`
where
floor = "floor_value"...
- 1,661
3
votes
3
answers
342
views
Are MySQL where clauses linear. How to optimise
Supposing I had a product database of some 50,000 products supplying data to a back end system and a website, some are live, some are archived and some are “switched off” as far as the website is ...
- 7,391
0
votes
1
answer
899
views
Prevent massive sequential scan with datetime query
How can I filter queries by date to prevent massive sequential scan on a large database?
My survey app collects responses and each answer to a question in a survey is stored in a table ...
- 4,933
0
votes
3
answers
2k
views
How to optimize an "optimize" MYSQL query that takes a lot of time
I have a table (innodb) with 1 million new inserts (20GB) a week. I only need the data for 1 week, so I delete it after 7 days, so each day we delete around 3GB and insert 3GB new. That table is ...
5
votes
1
answer
836
views
Is the formula 2b* (1+⌈ log (dm )〖(nr)〗⌉) for the total of I/O access in merge-sort correct?
I am studying databases from the book Fundamentals of Database Systems, from authors Elmasri and Navathe, 5th edition, and they explain briefly external sort using merge sort in almost at the ...
- 115
0
votes
1
answer
828
views
Postgres EXPLAIN ANALYZE Total Time Appears to Exceed Sum of Parts
I'm trying to identify some performance bottlenecks in my Postgres queries and ran an EXPLAIN ANALYZE on a query to get some insights. The output of the query analysis is below:
Nested Loop (...
- 2,998
-1
votes
1
answer
4k
views
MongoDB - Save vs Update [duplicate]
I have around 400 fields in my collection (including both at top level as well as embedded), following is the nature of write queries:
All write queries always update single document and an average of ...
- 59
1
vote
1
answer
97
views
Which Index Should I Create when using multiple OrderBy
I'm using Entity Framework Core and Reading some data with thousands of records. Each records has many columns, of that columns I'm using 3 (a, b and c) and doing:
OrderBy(a).ThenBy(b).ThenBy(c);
...
- 15.6k
0
votes
1
answer
872
views
How to optimize SQL Server table with 320 million + rows with only varchar(max) data types
I have a table with 320 million+ rows and 34 columns, all of varchar(max) datatype, and with no indexing.
I am finding it extremely time consuming to summarize the whole table. Can anyone suggest ...
- 11
-1
votes
1
answer
144
views
database optimization strategy for very large tables and expensive delete operations
Our Database has a very large table with several million rows of data. Some old code was written from a naive standpoint by myself several years ago, and doesn't handle for poor database performance. ...
- 1,659
-1
votes
1
answer
120
views
MySQL - Query very slow
I have statements like this that are timing out:
SELECT COUNT(*) FROM A WHERE A.value1 IN (
SELECT A.value1 FROM A WHERE A.value2 = 0
)
Table A has 13,000,000+ rows in it, and because of some ...
- 41
-2
votes
2
answers
48
views
MySql Query Timed Out in Live but not in Local
We are using MySQL InnoDB.
We have a query looks like this.
In our live environment, this query took more than 30 seconds to complete.
select count(*) as aggregate
from `parents`
where ...
- 1
0
votes
0
answers
137
views
Database profiler for postgresql server to get the detail of each query [duplicate]
We need a database profiler for our application which shows the full detail of each query and suggest for indexes etc I found is there any free tool available like Postgres Enterpirse Manager?
- 4,013
0
votes
2
answers
224
views
How to optimize this query? Slow query
Hi I am Trying to Optimize this Query.
If there are a lot of transactions in the timeframe it can take up to 10 Seconds to execute on my local environment.
I tried to create a Index on the created_at ...
- 25