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.

learn more… | top users | synonyms (2)

2
votes
1answer
375 views

Index strategy for a Queue Table, getting rid of filesort

I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is ...
4
votes
2answers
1k 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 ...
2
votes
1answer
256 views

What would be a optimal index for this query in mysql

I'm trying to optimize this query. SELECT * FROM sales_reps WHERE account_id = 1 AND (state = 'enabled') AND (clients >= 5 OR revenue >= 10000) ORDER BY name, platform, client_group, ...
5
votes
1answer
1k views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
1
vote
2answers
54 views

Do I need an index on this?

I have a sql server db and have a question regarding foreign key indices. Here's a snippet from the db: In programme_affiliates, I have a computed field, this is always set to '2' in this ...
3
votes
2answers
705 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
1answer
677 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 ...
6
votes
2answers
4k 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 ...
4
votes
1answer
5k views

Indexing SQL Server datetime Columns for Performance

Will adding an index to a datetime column increase performance if a WHERE myDateColumn BETWEEN @startDate and @endDate clause is frequently applied to that datetime column? If yes, should the index ...
5
votes
2answers
1k views

Should I rely on Database Engine Tuning Advisor for creating indexes?

How should I rely on Database Engine Tuning Advisor for creating indexes and statistics? I find more often than not, it generates overly complex indexes that (I think) would be more costly in index ...
2
votes
2answers
1k views

IN Clause causes Execution plan to change from Nested Loops to Hash Match

I'm tuning a query and have discovered some behaviour I'm not clear about. If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes. There only 7 rows returned in the ...
11
votes
2answers
409 views

Minimizing Indexed Reads with Complex Criteria

I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such: CREATE TABLE TICKETS ( TICKET_ID id PRIMARY KEY, JOB_ID id, ACTION_ID id, STATUS str256 ...
1
vote
1answer
180 views

Finding exact row in Clustered index leaf page

When traversing through Clustered index in Sql Server, the engine will start from Root page and traverse over non leaf nodes until it reaches leaf level page (data page). Every data page has row ...
1
vote
1answer
287 views

INDEX Creation Advice Oracle

I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id. The attributes date, and entity_id would make a unique pair. ...
23
votes
2answers
28k views

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an ...
5
votes
1answer
3k views

How are multiple indexes used in a query by MySQL?

Imagine a table where there are separate indexes on columns a and b. The query runs somewhat like this: SELECT col1, col2 FROM <table> WHERE a = 'value_1 AND b = 'value_2'; Now, does this ...
3
votes
1answer
366 views

For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?

Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns? Like if I have this table: CREATE TABLE [dbo].[tbl_shipping_boxes] ( ...
4
votes
1answer
1k views

Does creating an index on BIT(n) attribute in MySQL make sense?

I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2. I've seen some mentions on the internet that BIT ...
-3
votes
1answer
6k views

What are performance benefits when using number type instead of using char/varchar?

From various posts, I have summarized about using number type instead of char/varchar for PK: If you choose char/varchar type for PK and your values are short (just some alpha numberic codes) then it ...
2
votes
1answer
201 views

Aren't two writes required to update a clustered index record

I was reading the article on indexes at simple-talk, where it is written that If a heap has a non-clustered index on it (as the primary key), and data is inserted into the table, two writes have ...
1
vote
2answers
830 views

Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped?

everything is in the question. I have a table named tableA on which we have indexes and foreign keys and constraints. I have to modify content in a column called column1. This being a production ...
0
votes
1answer
57 views

Why $in is much faster then $all

I am well aware of what those 2 do. $in take .3 seconds. And $all take 26 seconds. Huge different. If anything $in should take longer because it returns more data. Also if the limit is removed, $in ...
3
votes
1answer
465 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 ...
14
votes
3answers
3k views

Is there any benefit to defragmenting SQL indexes in a SAN environment?

Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records. We have been running Ola Hallengren's index maintenance scripts weekly, and ...
1
vote
1answer
1k views

How to interpret this “db.explain()” for this mongodb query

db.tablebusiness.find({ "LongitudeLatitude" : { "$within" : { "$centerSphere" : [[106.772835, -6.186753], 0.0089967464101566] } }, "indexContents" : { "$all" : ["warung"] }, "Prominent" : { "$gte" : ...
4
votes
1answer
1k views

How are indexes represented in an ERD?

What do indexes look like in an Entity Relationship Diagram? I've googled it and I'm unsure what the standard look of an index is in an ERD. And I'm talking about the crowsfoot diagram that includes ...
5
votes
2answers
4k views

Best value for fill factor in index

I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also ...
8
votes
2answers
13k views

Why is this query not using my nonclustered index, and how can I make it?

As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default. This query runs in about 2.5 seconds: SELECT TOP 1000 * FROM ...
1
vote
2answers
471 views

Is Log Shipping or Mirroring a better solution for Disaster Recovery?

I am planning an index rebuild, however my logs are prone to huge overblown sizes. In fact I was leaning towards doing an intelligent re-indexing, but since the fragmentation levels are greater than ...
4
votes
2answers
1k 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 ...
2
votes
2answers
841 views

rebuilding indexes and logshipping?

We all know that index rebuilding creates lot of log growth..so what changes would you consider to your logshipping side to accomodate this log growth? Do we increase the frequency of the ...
2
votes
1answer
4k views

PAD_INDEX and FILLFACTOR on clustered Identity index

I am trying to work out what setting for PAD_INDEX I should use for a clustered index on a IDENTITY column, the table is rarely going to be updated after first insert so I am looking for the setting ...
3
votes
2answers
611 views

MySQL InnoDB Index in swap

I can't find any info regarding my question. This is more theoretical question. For example, i have table keywords. CREATE TABLE IF NOT EXISTS `keywords` ( `kid` int(11) unsigned NOT NULL ...
3
votes
1answer
1k views

MySQL query not using an index when table contains many records

I have a table: CREATE TABLE `p` ( `id` BIGINT(20) unsigned NOT NULL, `rtime` DATETIME NOT NULL, `d` INT(10) NOT NULL, `n` INT(10) NOT NULL, PRIMARY KEY ...
1
vote
3answers
1k views

Improve performance of a Fact Table

I have a Fact table CardTransactionFact Table Structure TABLE [dbo].[CardTransactionFact] [CardTransactionID] [int] IDENTITY(1,1) NOT NULL, [TransactionTerminalID] [int] NOT NULL, ...
6
votes
2answers
7k views

How can I improve the speed of a query on a 20 million+ row table?

I have a query that is used for getting internet traffic statistics of certain IP addresses. There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
4
votes
1answer
395 views

Improve query performance with index

I am looking to create an index for a database to acheive better performance. I am using this query: SELECT DISTINCT t1.* FROM current_order AS t1 LEFT JOIN receipt AS t2 USING ...
4
votes
1answer
1k views

How do I reindex a MYSQL table using a different disk?

I have my mysql data and index files on disk1 I need to reindex the table and do NOT have enough disk space on disk1 How do I reindex a table on disk1 and use disk2 for the reindex disk space? For ...
6
votes
2answers
3k 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 ...
6
votes
1answer
498 views

Can spatial index be a part of a multiple columns index?

This manual over here is extremely unclear and doesn't even provide some sample SQL statements: http://dev.mysql.com/doc/refman/5.6/en/column-indexes.html Another way to rephrase the question is the ...
8
votes
2answers
204 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
5
votes
2answers
849 views

Partition Function vs. Clustered Index

We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind ...
3
votes
1answer
9k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
2
votes
2answers
447 views

SQL Server and Over-Indexing Tables [duplicate]

Possible Duplicate: Where can I find some guidance on index strategies? I remember taking a course on SQL Server 2000, and the instructor said that you have to be careful of adding too many ...
5
votes
1answer
310 views

Query for data that is not there

I have this setup attendance table: ------------------------------------------ | member_id | attendance_date | event_id | ------------------------------------------ attendance indeces ...
0
votes
1answer
889 views

InnoDB - Use combined index with primary key on GROUP BY

As far as I know, an InnoDB index always contains the primary index. I cannot get the following to work: SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid WHERE t1.b = 99 GROUP ...
8
votes
2answers
2k views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
3
votes
1answer
263 views

I have Fragmented Index on a empty table!

I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be ...
3
votes
2answers
1k views

How does compound index help speed up query?

Say we have a bunch of column in mysql table. Latitude Longitude Say we make an index (Latitude and Longitude) Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort ...
6
votes
3answers
2k views

Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance

We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like PK BIGINT ID VARCHAR(50) Col1 Col2 etc So we need to insert a row in the ...