FAQ Database Discussion Community


Postgres does not use an index even less than 5% rows are returned

database,postgresql,indexing,database-performance
I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | +---------+-------------+-------------+----------+---------+---------+ |abcd12344| 4124135 |[1,2,53,1241]| apple | banana | papaya | +---------+-------------+-------------+----------+---------+---------+ id - random alphanumeric value<br> timestamp - epoch timestamp<br> numvalues - array of integers<br> text(n) -...

Speed up view performance

sql-server,indexing,view,database-performance
I have an old view that takes 4 mins to run, I have been asked to speed it up. The FROM looks like this: FROM TableA CROSS JOIN ViewA INNER JOIN TableB on ViewA.Name = TableB.Name AND TableA.Code = TableB.Code AND TableA.Location = TableB.Location WHERE (DATEDIFF(m, ViewA.SubmitDate, GETDATE()) = 1)...

Multiple MySQL DB connections in PHP good or bad practise?

php,mysql,performance,performance-testing,database-performance
I want to add a new discussion area to my website in which users will be able to add topics and receive comments. I thought I will add a new DB just for the topics and comments to avoid my main DB becoming very large. But, I will need to...

Does the size of a document affect performance of a find() query?

database,node.js,mongodb,nosql,database-performance
Can the size of a MongoDB document affect the performance of a find() query? I'm running the following query on a collection, in the MongoDB shell r.find({_id:ObjectId("5552966b380c2dbc29472755")}) The entire document is 3MB. When I run this query the operation takes about 8 seconds to perform. The document has a "salaries"...

DB Index not being called

oracle,performance,oracle11g,oracle10g,database-performance
I know this question has been asked more than once here. But I am not able to resolve my issue so posting it again for help. I have a table called Transaction in Oracle database (11g) with 2.7 million records. There is a not-null varchar2(20) (txn_id) column which contains numeric...

Extra Time not reflected in PostgreSQL EXPLAIN ANALYZE?

postgresql,database-performance
Turning on \timing in PostgreSQL, I can see the time it takes a command to run. I've run the same query, with the same database and indexes on two machines and am seeing very different results. On the first machine: machine1=# EXPLAIN ANALYZE INSERT INTO "notes" ("content", "date", "inserted_at", "updated_at",...

Rails :counter_cache with custom query association

ruby-on-rails,ruby-on-rails-4,activerecord,rails-activerecord,database-performance
I have a model like this: class Vote belongs_to :content, counter_cache: true end class Content has_many :votes has_many :votes_up, -> { where(positive: true) }, class_name: 'Vote' has_many :votes_down, -> { where(positive: false) }, class_name: 'Vote' end If I create a proper migration, when I execute mycontent.votes_count, it will not make...

Execute multiple functions together without losing performance

postgresql,plpgsql,database-performance,query-performance,postgresql-performance
I have this process that has to make a series of queries, using pl/pgsql: --process: SELECT function1(); SELECT function2(); SELECT function3(); SELECT function4(); To be able to execute everything in one call, I created a process function as such: CREATE OR REPLACE FUNCTION process() RETURNS text AS $BODY$ BEGIN PERFORM...

optimizing child/parent structure in one table with a lot of data

sql,performance,postgresql,database-performance
I have a table which has a simple parent child structure products: - id - product_id - time_created - ... a few other columns It is a parent if product_id IS NULL. Product id behaves here like parent_id. Data inside looks like this: id | product_id 1 NULL 2 1...

Speed up SQL SELECT with arithmetic and geometric calculations

mysql,sql,database-performance,mariadb
This is a follow-up to my previous post How to improve wind data SQL query performance. I have expanded the SQL statement to also perform the first part in the calculation of the average wind direction using circular statistics. This means that I want to calculate the average of the...

Are parenthesis needed to improve performance in postgresql?

sql,postgresql,database-performance
Is really true that parenthesis in all conditions in postgresql improve performance? Example: SELECT field_1, field_2, ... , field_n FROM table_x WHERE ((field_x = a) AND (field_y = b) AND (field_z = c)) ...

Do I have to drop the primary key or reload the table data for partitioning in MySQL InnoDB?

mysql,innodb,database-performance,database-partitioning,pruning
I have to partition a few tables by date range. Do I have to drop the primary or reload the complete data to be able to partition these tables? Some of the tables do contain a lot more than 50 million rows. alter table temp_table_test1 partition by range (unix_timestamp(created_at)) (...

mysql - how to insert multiple records and delete remaining ones

mysql,query-optimization,database-performance
Lets start with an example. There's a table: CREATE TABLE X ( id INT(32) NOT NULL auto_increment, account INT(32), value INT(32), primary KEY (id) ) What I want to achieve is to ensure that given account has specified set of values (and only these values). Basically, how to do below...

Avoid unique conflicts on insert SQL SERVER

sql,sql-server,performance,database-performance
I'm trying to insert into a table that has unique constraint on (ProductType, ProductOwnerid) in a way that when that key already exists that the contraint violation would not be triggered. So I have this SQL that works as intended: INSERT INTO dbo.Products (ProductType, ProductOwnerId) SELECT TOP 1 22, 44...

Followers - mongodb database design

javascript,mongodb,database-design,database-schema,database-performance
So I'm using mongodb and I'm unsure if I've got the correct / best database collection design for what I'm trying to do. There can be many items, and a user can create new groups with these items in. Any user may follow any group! I have not just added...

Compare multi values against multi values, maintaining performance

c#,performance,linq,tsql,database-performance
I have two forms of records or data. One being Inventory Record and the other a Product. Both the Inventory Record and Product Record have Code values stored in separate tables. Each record has a value record and is foreign keyed to the main table. I am trying NOT to...

Select / order by desc / limit query speed

mysql,database-performance
I see a big difference in speed between these two queries; the first one runs in 0.3 seconds, and the second in 76 seconds. The first query only selects the key, whereas the second query select an additional field, which is an int(11). I can substitute the second field for...

Best approach for executing massive insert select from statements

sql,oracle,oracle11g,database-performance
I have over 100k insert select from statements which I will break into smaller batches. I came up with two approaches and not sure which one is more effective. Please give me your thoughts. I could use APPEND hint but I have to put a Commit after each insert statement...

Android SQLite alternatives

android,sqlite,android-sqlite,database-performance
I'm building a simple android RPG. Each player has access to a maximum of about 50 soldiers. Each soldier has some stats (class, hp, attack, etc), and three inventory slots. There will be only about 100 items in the game to fill these inventory slots. I was hoping to set...

Improve MySQL perforance by indexing everything

mysql,sql,indexing,database-performance
By indexing a column, MySQL does not need to look through every row in the table, instead it can find the data you are searching for alphabetically, then skip immediately to look at the row(s) where the data is located. So I'm starting to think that, by setting every columns...

SQL queries optimization

mysql,database,performance,database-performance
I'm having trouble optimizing some sql queries that take in account datetime fields. First of all, my table structure is the following: CREATE TABLE info ( id int NOT NULL auto_increment, name varchar(20), infoId int, shortInfoId int, text varchar(255), token varchar(60), created_at DATETIME, PRIMARY KEY(id) KEY(created_at)); After using explain on...

MongoDB Projection Performance - or just many small documents?

mongodb,database-performance,projection,query-performance
I'm building a collection for User Settings / Preferences and other user related stuff. At the moment a document look like this: { "_id": "USER_ID", "setting1": { "foo": "bar" }, "setting2": true, "setting3": "this might be a huuuuge email template", ... } And I query stuff by the user's id...

MySQL inserts very slow into MEMORY table

mysql,sql-insert,database-performance
I am trying to optimize a large (68K row) insert into a staging table. I created the table as a MEMORY engine table, and there are no indexes or foreign keys at all. When my ETL process starts inserting, the inserts execute, but very slowly; the full load takes over...

Why am I seeing 'reads' when incrementing a counter in Cassandra?

cassandra,erlang,database-performance
I am using Cassandra to hold counter fields for some analytics that we need to process. I noticed that for a given write event (where a bunch of counters are incremented), there seems to be a 'read' operation. This means that the reads are queuing while waiting for the 'write'...

Using Distinct or Group by in SQL . Which one is better and faster? State it [closed]

sql-server,sql-server-2008,group-by,distinct,database-performance
I'm Just using the table which having more than 200 thousand records each.I need to Fetch Unique records by joining those tables .Which keyword would work faster in this case using Distinct or Group by ?.It should be looked from performance wise better and faster one.Kindly suggest me.