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
104
votes
15
answers
233k
views
Delete statement in SQL is very slow
I have statements like this that are timing out:
DELETE FROM [table] WHERE [COL] IN ( '1', '2', '6', '12', '24', '7', '3', '5')
I tried doing one at a time like this:
DELETE FROM [table] WHERE [COL] ...
- 34k
1
vote
1
answer
119
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
1
answer
396
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
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
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
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
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
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
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
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
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
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
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
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
3
votes
2
answers
22k
views
What is Ideal value to set for long_query_time in slow query log for magento?
To optimize magento we enable slow query log in mysql.
What is ideal value for query_long_time especially for magento?
- 307
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
911
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
15
votes
5
answers
9k
views
Optimal database structure - 'wider' table with empty fields or greater number of tables?
I need to fit in additional data into a database, and I have a choice between modifying an existing table (table_existing) or creating new tables.
This is how table_existing looks like right now:
...
- 8,325
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
2
votes
3
answers
1k
views
How much size I will save if changed INT column to MEDIUMINT?
I'm learning how to optimize my database by re choosing the correct data types for the columns and I want to know how much size I will save if I choose MEDIUMINT (3 Bytes) instead of INT(4 Bytes)
...
- 7,613
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
-1
votes
3
answers
108
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
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
827
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