Stack Overflow
Stack Overflow

Questions tagged [database-optimization]

186 questions
2
votes
3 answers

What do I need to know about databases in order to create a quality Django app?

I'm trying to optimize my site and found this nice little Django doc: Database Access Optimization, which suggests profiling followed by indexing and the selection of proper fields as the starting point for database optimization. Normally, the…
Matt Parrilla
  • 2,901
  • 4
  • 30
  • 50
2
votes
2 answers

MS SQL : The best way to delete rows from a ginormous table

I have a very large table [X], which has 170 million rows, and we need to archive data to keep only used records in [X]. We are doing this to keep our system fast as it is slowing down. We are only using a small amount of rows from the whole table…
Mez
  • 4,365
  • 4
  • 24
  • 52
2
votes
1 answer

Is there a performance benefit to creating a multiple index on a primary key + foreign key?

If I have a table that has a primary key and a foreign key, and searches are frequently done with queries that include both (...WHERE primary=n AND foreign=x), is there any performance benefit to making a multiple index in MySQL using the two…
Carvell Fenton
  • 2,253
  • 6
  • 22
  • 28
2
votes
3 answers

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 concerned (available only in back end admin for some…
Jamie Hartnoll
  • 6,673
  • 12
  • 53
  • 89
2
votes
3 answers

Increase postgresql-9.5 performance on 200+ million records

I have 200+ millions of records in postgresql-9.5 table. Almost all queries are analytical queries. To increase and optimize the query performance so far I am trying with Indexing and seems that its not sufficient. What are the other options i need…
Rishikesh Teke
  • 398
  • 2
  • 8
2
votes
1 answer

Django: retrieve single object from ManyToMany relation effectively

I have a database structure which can be simplified as following (Version and AdditionalInfo not shown since not directly related to my question): class Image(models.Model): file = models.ImageField(blank=False, null=False,…
m5seppal
  • 1,046
  • 3
  • 13
  • 28
2
votes
3 answers

MySql querying taking too long

I have a table CREATE TABLE temp ( i1 DECIMAL(10, 5), i2 DECIMAL(10, 5), i3 DECIMAL(10, 5), i4 DECIMAL(10, 5), i5 DECIMAL(10, 5), i6 DECIMAL(10, 5), i7 DECIMAL(10, 5), i8 DECIMAL(10, 5), i9 DECIMAL(10, 5), o1…
User9813
  • 31
  • 1
  • 9
2
votes
1 answer

Postgres won't use index depending on specific value of id in where clause

I've been tinkering/reading for a while, but can't find any optimizations that work here... I've indexed the relevant ids in the joins, I tried a manual vacuum, and I also tried clustering on an index so that maybe the query optimizer wouldn't…
dcc310
  • 690
  • 6
  • 13
2
votes
1 answer

Does GAE Datastore support eager fetching?

Let's say I want to display a list of books and their authors. In traditional database design, I would issue a single query to retrieve rows from the Book table as well as the related Author table, a step known as eager fetching. This is done to…
Tony the Pony
  • 37,471
  • 63
  • 170
  • 273
2
votes
3 answers

Deleting rows from SQL Server increased the database size

I have written some code in C# that deletes records from a database. It's your straightforward SQL delete code enclosed in a transaction. using (SqlTransaction trans = conn.BeginTransaction()) { //some delete code trans.Commit(); } I have…
jmc
  • 1,477
  • 4
  • 21
  • 44
2
votes
1 answer

How to make table partition by created date in rails

Well, I have database ( Postgres ) and Rails as web-framework. I want to know how to do table partitioning. Ok, suppose, that I have table: Events and it has field created_at This model use act_as_paranoid gem and these events didn't deleted at all.…
gaussblurinc
  • 3,476
  • 8
  • 31
  • 61
2
votes
2 answers

Postgres not using index when using date() function

My schema is CREATE TABLE a ( id BIGINT PRIMARY KEY, dt TIMESTAMP NOT NULL ); and I have created an index on dt: CREATE INDEX a_dt_index ON a (dt); The index is working fine when I use a query like SELECT * FROM a WHERE dt >=…
lukstei
  • 805
  • 2
  • 8
  • 19
2
votes
1 answer

Does SSD obsolete following scenario

There is a scenario that if a query can fetch a large portion of table (like 50%), then optimizer would not choose index even if index is applicable, instead optimizer would use table scan. Someone said that for SSD, above optimization does not…
goodier
  • 355
  • 4
  • 17
2
votes
1 answer

Is there any performance difference in MySQL using default vs setting the value in the query?

I am wondering if its better to set default values for the table or set the in the values in the query performance-wise? Or is there any difference? For example using default value of CURRENT_TIMESTAMP for timestamp field. Versus no default and…
Firze
  • 3,283
  • 4
  • 41
  • 58
2
votes
1 answer

Django - Displaying result information while optimizing database queries with models that multiple foreign key relationships

So I'm trying to put together a webpage and I am currently have trouble putting together a results page for each user in the web application I am putting together. Here are what my models look like: class Fault(models.Model): name =…
BoogeyMarquez
  • 77
  • 1
  • 5
1 2
3
…
12 13