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
1answer
407 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
683 views
Create index if it does not exist
I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?
This is a similar issue ...
4
votes
2answers
170 views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about 1MM ...
4
votes
1answer
505 views
What is Sql Server Clustered Index in Oracle terminology
A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on.
If you look up with a name Ozgur you start O then z etc.
I would like a simple explaination for this ...
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 ...
3
votes
1answer
149 views
index not being used
I have an exact same myisam table on master and slave. The indexes are not being used on Master server.
mysql> explain select count(date_time) from ox_data_summary_ad_hourly where date_time = ...
3
votes
1answer
173 views
Minimum rowcount for filtered index?
Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known.
But with the advent of ...
2
votes
1answer
37 views
Do I need to reenter old data after adding an index to a table?
I want to add index to my tables. Some of my tables already have couple thousand rows.
Do I need to reenter my stored data after adding index to columns (to make them aware of index/so the indexing ...
2
votes
3answers
82 views
Programmatically find indexes that cannot be rebuilt online
I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.
Is ...
2
votes
2answers
57 views
Are there implicit indexes in InnoDB like MyISAM?
If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys:
(a)
(a, b)
(a, b, c, id)
Is this valid for ...
2
votes
2answers
163 views
In MySQL, should I add an index even if the query that scans the table is only ran once a month?
Concretely, is it worth it to add an index to a query that scans the table of a mysql database,
even if that index would only be used once a month?
For example, having a users a table and then ...
1
vote
1answer
93 views
Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index
One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence ...
1
vote
1answer
88 views
Should indexes automatically be placed into their own bufferpool?
We work with DB2 LUW (specifically we are currently on 9.7 FP4) running on AIX.
Per best practices from IBM, they recommend that when you build a table, you place the data, the indexes, and LOBs/LONG ...
1
vote
2answers
140 views
How to index this table (a_level, b_level, item_id)
I have a table of items, each of it has an a_level, b_level, and an item_id. Any b_level is dedicated to only one a_level (example: b_level 14 is "child" of a_level 2 only)
Lets say we have million ...
