A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns but structures the data differently to allow for faster access.
4
votes
2answers
1k views
Determine Index Compression Candidates Online
By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression:
ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE;
...
4
votes
1answer
4k views
SQL Server: index creation date
In SQL Server 2005 and above, how can I find when an index was created?
4
votes
2answers
69 views
Should I use a composite or single-column index?
I have the following columns in my database table (Medicines).
ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.
I am making a stored ...
4
votes
2answers
131 views
What happens to the index of a primary key after a DROP CONSTRAINT?
I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the ...
4
votes
2answers
250 views
Why index REBUILD does not reduce index fragmentatation with a page count > 1000?
This is kind of a followup to the existing question on this site "Why index REBUILD does not reduce index fragmentatation?". The accepted answer to that question, and all other resources I have found ...
4
votes
1answer
311 views
How does SQL Server choose an index key for a foreign key reference?
I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade. ...
4
votes
1answer
111 views
Filtered indices: Why include the filtered-on field?
This just drives me mad.
Consider a simple table with irrelevant columns removed:
create table boxes (
row_id int not null identity(1,1) primary key,
location varchar(15) null,
dismantled bit ...
4
votes
1answer
190 views
Strictly only RAM matters?
I have to work on a project which would have a extra large table with billions of records but only two fields one is id and another is text.
And id would be set to BIGINT and PK then if 8+2 (extra 2 ...
4
votes
1answer
885 views
SQL Server 2008 R2 reindex job reorganise getting stuck
I am currently experiencing this job getting stuck in my nightly maintenance solution -
ALTER INDEX [PK_ThirdPartySettleCalled] ON [evo_archive].[dbo].[ThirdPartySettleCalled]
REORGANIZE WITH ...
4
votes
1answer
624 views
How can I get status messages during a large re-indexing operation with MySQL 5.5?
I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and ...
4
votes
1answer
132 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
4
votes
2answers
125 views
Inner join using an array column
Having trouble indexing and executing a query in O (log n) time.
The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
4
votes
1answer
67 views
How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
4
votes
1answer
78 views
After Rackspace server creation, PostgreSQL query planner doesn't work as expected
We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
4
votes
1answer
200 views
Indexing strategy when using the between operator SQL Server 2008
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
[pat_id] [int] NOT NULL,
[fill_Date] [date] NOT NULL,
[script_End_Date] AS ...
4
votes
2answers
356 views
Could running ANALYZE on all databases on large InnoDB tables show some performance increase?
So we have an old database that was fragmented until we ran OPTIMIZE on all tables.
The database/tables are about 100GB in size. Tables are InnoDB with heavy read/write activity.
There are so many ...
4
votes
4answers
187 views
Can I reorganise index on a sql server 2005 database without performance hit on production server
I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. ...
4
votes
1answer
347 views
How to index dynamic attributes in MongoDB
I have following kind of data (simplified a bit from my real case) in MongoDB:
{
"name":"some name",
"attrs":[
{"n":"subject","v":"Some subject"},
{"n":"description","v":"Some ...
4
votes
1answer
425 views
SQL Server 2008 query planner failing after index drop & recreate
Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
4
votes
1answer
128 views
MySQL table design of logging tables
I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization.
Currently we have a table that logs session data, it writes about ...
3
votes
3answers
315 views
Is a surrogate key better than a natural key in this case
I copied this code from here:
CREATE TABLE records(
email TEXT REFERENCES users(email),
lat DECIMAL,
lon DECIMAL,
depth TEXT,
upload_date TIMESTAMP,
comment TEXT,
PRIMARY ...
3
votes
1answer
307 views
How are bit fields sorted for an index?
I am building a compound index, and I'm not 100% sure which way to include a bit field that is part of the table. The index will be for something similar to a list of forum topics, where I want the ...
3
votes
2answers
341 views
Do I risk losing the benefits of indexing if I have an index on every column?
I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
3
votes
3answers
389 views
Does this computed CHECKSUM() index design make sense?
I have come across what looks to me like a slightly odd pattern in a SQL Server 2005 database I'm taking care of, and was wondering whether it's just me, or whether it really is odd.
There are a ...
3
votes
2answers
236 views
Performance impact of having gaps in Identity Column in SQL Server 2005
We have a number of tables in our database with an identity column as the primary key & clustered index. Over the years records have been added and deleted from these tables and of course there ...
3
votes
2answers
716 views
How does Oracle handle composite index lookups?
MySQL cannot use a composite index in a lookup in which the WHERE condition doesn't include the columns forming a left-most prefix:
MySQL cannot use the index to perform lookups if the columns do ...
3
votes
1answer
97 views
How to update views outside of update/insert transaction in SQL Server
Looking at SQL Server indexed views I see that these views are updated within a transaction that updates dependent tables. I wonder if there is a way around it?
There will be about 10 views with 20 ...
3
votes
1answer
285 views
Surrogate key / Primary key: Better to use an existing unique data field or create a key field?
I am not sure if this question has been asked or not. At least I couldn't find it.
I am curious about a primary key in terms of efficiency with data searching and retrieval.
This is a hypothetical ...
3
votes
2answers
575 views
Index on primary key not used in simple join
I have the following table and index definitions:
CREATE TABLE munkalap (
munkalap_id serial PRIMARY KEY,
...
);
CREATE TABLE munkalap_lepes (
munkalap_lepes_id serial PRIMARY KEY,
...
3
votes
2answers
161 views
What is the signifigance of a fillfactor of 20?
I have a database for our ticket system in SQL Server express. I was poking around and noticed that a lot of the indexes have a fillfactor set to 20. What is the signifigance of it being set to 20? ...
3
votes
1answer
557 views
Which fillfactor is used by ALTER INDEX REBUILD?
From technet I got the impression, that I either have to explicitly set the wanted fillfactor or when I do not specify it, the default fillfactor 0 is choosen.
For tables I know to get rows added ...
3
votes
2answers
271 views
Index max row size error
Is there a upper bound for an array column?
I am getting this error when inserting into the array field -
PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data"
Here's my ...
3
votes
3answers
3k views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
3
votes
2answers
248 views
Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table?
I have read that when having a lot of indexes on a database It can seriously hurt the performance but in the PostgreSQL doc I can't find anything about it.
I have a very big table with something like ...
3
votes
2answers
623 views
Rebuilding index has impact on mdf/ldf files SQL Server 2008
While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files.
What does it exactly means?
Is complete process done on TempDB and does ...
3
votes
2answers
290 views
SQL Server 2012 - Frequent SELECT and UPDATE on bit column. Index doesn't help
I have a SQL Server table with about 2 million rows in, this table stores short textual documents. Here's my schema:
CREATE TABLE Documents (
documentId bigint IDENTITY NOT NULL,
content ...
3
votes
1answer
131 views
Is UNIQUE(), INDEX() different from UNIQUE INDEX()?
Is there any different how mysql treats
UNIQUE (column1, column2),
INDEX (column1)
and
UNIQUE INDEX (column1, column2)
and
UNIQUE (column1, column2)
In the latter, I think mysql will ...
3
votes
2answers
115 views
What would be the best way to model my simple table?
What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4?
Or, searching for the right row through a table with only 1 column with ...
3
votes
1answer
3k views
How do I alter index rebuild an entire database?
I want to reorganize the indexes of all tables in my SQL Server 2008 database, named 'db'. I try the following command:
ALTER INDEX all ON db REBUILD
And I get the following error:
Cannot find ...
3
votes
2answers
61 views
Performance of different precedence pseudocolumn solutions with LIMIT
I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests.
The OP wanted to get the first matching row given ...
3
votes
1answer
214 views
Move Primary Key to Filegroup (SQL Server 2012)
How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient:
Drop non-clustered indexed (requires them to be resorted and ...
3
votes
2answers
400 views
Performance degradation after rebuilding indexes
I am optimizing some server application task that uses database (querying, complex calculations, data insertions...). Execution of this task spends about 16 minutes (I have tested it 3 more times) and ...
3
votes
3answers
808 views
When do hash indexes become reasonable?
MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking.
And it seems to be widely understood that if the text field is ...
3
votes
2answers
87 views
Reducing Log Impact During Re-Indexing
We use Ola's maintenance solution and its great.
Regardless of method for re-indexing a major friction point with IT is the amount of log generated during the weekly re-indexing process. For a 1TB ...
3
votes
1answer
126 views
Index Key Column Order
I have created a joining table for many-to-many relationship.
The table only has 2 cols in it, ticketid and groupid
typical data would be
groupid ticketid
20 56
20 87
20 ...
3
votes
2answers
130 views
Proper Index for WHERE X <> 1 AND Y <> 1 ORDER BY Z
I have a relatively large table that is accessed in exactly one way, by exactly one query (on the read side).
The query filters the table by two columns (both bit), and sorts by a third column (an ...
3
votes
2answers
96 views
Why is this key lookup required?
I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm ...
3
votes
1answer
283 views
Is a clustered index locked while a (clustered index) scan is in progress?
The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this.
The problem leading me to this question is that I have a view joining several ...
3
votes
1answer
74 views
Find least recently used mysql table index
I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables.
While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if ...
3
votes
2answers
300 views
Slow ORDER BY with LIMIT
I have this query:
SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
@@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount
I'm happy with it:
"Sort ...
