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.
5
votes
3answers
175 views
When you select from a table on an indexed column and order by that column in the opposite direction of the index, does the index help
Let's say I have a large table of transactions with a date field and an index on that field in ascending order. If I select that field from the table and order by in reverse order is it able to ...
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
740 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
176 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
512 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
39 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
84 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
59 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
164 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
94 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
89 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 ...
