Stack Overflow
Stack Overflow

Questions tagged [database-optimization]

186 questions
81
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
  • 28,411
  • 33
  • 120
  • 167
34
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
  • 48,267
  • 47
  • 159
  • 244
24
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
  • 48,267
  • 47
  • 159
  • 244
15
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
  • 9,832
  • 13
  • 42
  • 65
11
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
  • 7,593
  • 15
  • 64
  • 105
8
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,411
  • 4
  • 21
  • 39
7
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
  • 11,157
  • 16
  • 58
  • 108
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
  • 12,634
  • 7
  • 38
  • 62
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
  • 42,600
  • 24
  • 134
  • 225
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,141
  • 10
  • 58
  • 59
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
  • 15,760
  • 32
  • 114
  • 184
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…
Click Upvote
  • 235,452
  • 251
  • 553
  • 736
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
  • 3,619
  • 5
  • 29
  • 43
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
  • 103
  • 5
4
votes
3 answers

Inventory Database Design

I'm fairly new to using MySQL and I'm trying to understand what the most efficient way to store a player's inventory items in my database is. So here's the setup: There's a table called 'player', and each player is assigned a unique 'playerid' which…
Matthew
  • 391
  • 1
  • 4
  • 14
1
2 3
…
12 13