207 questions
0
votes
1
answer
111
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
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
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,
...
- 659
-1
votes
1
answer
47
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
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 ...
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
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
0
votes
1
answer
62
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
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 ...
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
1
vote
1
answer
137
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
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
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
-2
votes
1
answer
631
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
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
2
answers
67
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
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
435
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
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
0
votes
1
answer
898
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
5
votes
1
answer
834
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
-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
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
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
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