Stack Overflow
Stack Overflow

Questions tagged [database-optimization]

201 questions
96
votes
15 answers

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] IN ( '1' ) and so far it's at 22 minutes and…
Kyle
  • 32,731
  • 39
  • 134
  • 184
54
votes
3 answers

Solution for speeding up a slow SELECT DISTINCT query in Postgres

The query is basically: SELECT DISTINCT "my_table"."foo" from "my_table" WHERE... Pretending that I'm 100% certain the DISTINCT portion of the query is the reason it runs slowly, I've omitted the rest of the query to avoid confusion, since it is…
orokusaki
  • 55,146
  • 59
  • 179
  • 257
27
votes
2 answers

How do I know when to index a column, and with what?

In docs for various ORMs they always provide a way to create indexes, etc. They always mention to be sure to create the appropriate indexes for efficiency, as if that is inherent knowledge to a non-hand-written-SQLer who needs to use an ORM. My…
orokusaki
  • 55,146
  • 59
  • 179
  • 257
18
votes
4 answers

Will indexing improve varchar(max) query performance, and how to create index

Firstly, I should point out I don't have much knowledge on SQL Server indexes. My situation is that I have an SQL Server 2008 database table that has a varchar(max) column usually filled with a lot of text. My ASP.NET web application has a search…
johna
  • 10,540
  • 14
  • 47
  • 72
12
votes
5 answers

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: table_existing ------------------------- | ID | SP |…
siliconpi
  • 8,105
  • 18
  • 69
  • 107
10
votes
5 answers

Is inserting a new database entry faster than checking if the entry exists first?

I was once told that it is faster to just run an insert and let the insert fail than to check if a database entry exists and then inserting if it is missing. I was also told that that most databases are heavily optimized for reading reading rather…
Holtorf
  • 1,451
  • 4
  • 21
  • 41
7
votes
1 answer

MySQL Optimization: EXPLAIN "Extra" column contains "Using Where"

So I always thought that seeing "Using Where" in the Extra column was a good thing. However, I was planning a brown bag lunch for my coworkers on intepreting EXPLAIN and now I'm not so sure. The MySQL doc says this in the notes about "Using…
Mike Sherov
  • 13,277
  • 8
  • 41
  • 62
6
votes
1 answer

One big query vs. many small ones?

I'd like to know, which option is the most expensive in terms of bandwith and overall efficiency. Let's say I have a class Client in my application and a table client in my database. Is it better to have one static function Client.getById that…
federico-t
  • 12,014
  • 19
  • 67
  • 111
6
votes
2 answers

Best way to process database in chunks with Django QuerySet?

I am running a batch operation over all rows in a database. This involves selecting every single model and doing something to it. It makes sense to split this into chunks and do it chunk by chunk. I'm currently using Paginator, because it's…
Joe
  • 46,419
  • 33
  • 155
  • 245
6
votes
8 answers

Improving performance of Sql Delete

We have a query to remove some rows from the table based on an id field (primary key). It is a pretty straightforward query: delete all from OUR_TABLE where ID in (123, 345, ...) The problem is no.of ids can be huge (Eg. 70k), so the query takes a…
amit
  • 10,612
  • 11
  • 61
  • 60
6
votes
3 answers

Speed up multi-dimensional euclid distance calculation in MySQL

I have the following table storing data about images: images - id (int) - sample_1_1 (int) - sample_1_2 (int) - sample_1_3 (int) - sample_2_1 (int) - sample_2_2 (int) - sample_2_3 (int) - ... # Up until sample_25_3 The task is to calcuate…
F.P
  • 17,421
  • 34
  • 123
  • 189
6
votes
3 answers

Should primary keys always be added to an innodb table?

I have some innoDbs with only 2 int columns which are foreign keys to the primary keys of other tables. E.g one table is user_items, it has 2 columns, userId, itemId, both foreign keys to user and item tables, set to cascade if updated or…
Ali
  • 261,656
  • 265
  • 575
  • 769
6
votes
1 answer

30 Million Rows in MySQL

Evening, I'm going through the long process of importing data from a battered, 15-year-old, read-only data format into MySQL to build some smaller statistical tables from it. The largest table I have built before was (I think) 32 million rows, but I…
Kohjah Breese
  • 4,008
  • 6
  • 32
  • 48
5
votes
1 answer

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 beginning of chapter 15. They divide the algorithm in two…
Ronald Becerra
  • 115
  • 5
5
votes
1 answer

What does SQL Server Execution Times represent?

I have a query that I'm running on two equivalent databases, but hosted on separate MS SQL 2005 servers. I want to measure the time of a query on both servers, and thus tried the following: SET STATISTICS TIME ON GO SELECT TOP 10000 * FROM…
Tomas Vinter
  • 2,690
  • 8
  • 36
  • 45
1
2 3
…
13 14