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
77 views
What is the difference between these two index declarations and do I need both?
I was examining a script with 'Display Estimated Execution Plan' and it made several recommendations for indexes to add to the database. I added them and the script did indeed run much faster. Now ...
1
vote
3answers
48 views
Nonclustered index is faster than clustered index?
Both tables have same structure and 19972 rows in each table.
for practicing indexing, i created both tables having same structure and created
clustered index on persontb(BusinessEntityID)
and
...
6
votes
1answer
50 views
Index usage stats DMV indicates no index activity
I'm trying to obtain information on indexes. According to several websites, the below TSQL query should join information on indexes to the indexes table, returning indexes and related information. ...
0
votes
1answer
43 views
Update performance: clustered versus covering index
I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table ...
1
vote
5answers
72 views
What happens when we create index
What happens exactly when I create an index on the empid column of this employee table
CREATE TABLE employee
(
empid number(5),
ename varchar2(30),
sal number(10,2)
);
Let's assume the ...
2
votes
1answer
81 views
Does the size of the primary key contribute to table size?
I have a table (InnoDB) with data length around 36G and index length 23G.
It had a composite primary key across three columns.
As an initial attempt to reduce the size of this table, I dropped the ...
1
vote
0answers
44 views
Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?
We have very large database table that I need to select the value of the max id. Given the table size SELECT MAX(id) FROM tableA will not perform well enough.
Table definition (with columns omitted ...
0
votes
0answers
21 views
How to/advice for hosting a multi-user database on the cloud with specific objectives
I have developed an application that users will be able to download and create an account with through our company. This application will always be connected to the internet. I have been testing the ...
1
vote
1answer
29 views
Rebuild an index while it is being referenced in Oracle
I have come across many articles on this site and on Tom Kyte's blog on the how, whys and whens of Re-building indexes. Note that this question is about whether I can rebuild when there are concurrent ...
1
vote
1answer
29 views
Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”
Environment:
Ubuntu 12.04 (and 13.04)
MySQL 5.6.11
I have a table which has a full text index on it (real table has much more columns and rows):
DROP TABLE IF EXISTS articles;
CREATE TABLE ...
3
votes
2answers
67 views
If I update a column record in a table, will indexes that do NOT have this column in it be affected?
In terms of performance if I have a table like so:
CREATE TABLE [TESTDATA].[TableA](
[Col1] [nchar](5) NOT NULL,
[Col2] [nchar](2) NULL,
[Col3] [float] NULL
CONSTRAINT [TableA_PK] PRIMARY ...
4
votes
0answers
54 views
Indexing strategy for dynamic predicate
Assume SQL Server 2012 Standard edition.
My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints).
The ...
3
votes
1answer
73 views
Index on foreign key makes query extremely slow
We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.
The queried table (table3) has an indexed PK, three FK with indexes ...
16
votes
6answers
1k views
Why don't databases create their own indexes automatically?
I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly ...
0
votes
1answer
15 views
MYSQL - Using simple inventory number convention as Primary Key
I have an inventory with three numbered groups like so:
Group 1: 0-999
Group 2: e0-e999
Group 3: v0-v999
Can I simply make a one-column table to store this information and make that column the ...
0
votes
4answers
76 views
MySql Order by isnull() Performance Problem
my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.
AddDate and Price has an index.
SELECT Id, ...
0
votes
2answers
52 views
Run query faster, what index to use
I have a table with 85M rows :
CREATE TABLE emissions_totales (
"schema_source" varchar(150),
"snap" varchar(20),
"gid" varchar(255),
"polluant" varchar(50),
"emi" float8,
"numreg" varchar(3),
...
5
votes
1answer
355 views
How to set SQL Server index pages per fragment?
I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx)
The ...
0
votes
0answers
16 views
How implement MySQL unique index with two foreign key that one can be null?
I would know if can be declared in MySQl to create an index constraints in a table with two foreign key that one can be null.
For example cannot be repeated a null foreign key with the other foreign ...
0
votes
1answer
54 views
Is index on foreign key used? (No difference in EXPLAIN.)
Essentially, I am joining two MyISAM tables and have found that the EXPLAIN output for the query does not change regardless of whether I have an index on the foreign key in the parent table. I'm not ...
1
vote
0answers
23 views
Oracle - Mysterious indexes
I have a table in Oracle 10.2g that I'm trying to import roughly 11 million rows into. The table contains an SDO_GEOMETRY column. It previously had NOT NULL constraints on all 3 columns, a primary key ...
0
votes
1answer
37 views
Will Partitions and Indexes on the same table help in performace of Inserts and Selects?
I have a table containing the list of visitors and this table has the following information.
Visitor Browser Information
Visitor Location Information
Visitor Time Information
No of Visits
I have a ...
4
votes
2answers
50 views
How to design indexes for columns with NULL values in MySQL?
I have a database with 40 million entries and want to run queries with the following WHERE clause
...
WHERE
`POP1` IS NOT NULL
&& `VT`='ABC'
&& (`SOURCE`='HOME')
&& ...
0
votes
0answers
34 views
Longest prefix search in Oracle
I have a list of phone number prefixes defined for large number of zones (in query defined by gvcode and cgi).
I need to efficiently find a longest prefix that matches given number PHONE_NR.
I use ...
6
votes
2answers
133 views
Does an index with multiple columns make a similar index redundant?
Let's say I have a table that looks like this:
CREATE TABLE Activity (
ActivityID int primary key identity(1,1) ,
ActivityName nvarchar(10),
InactiveFlag bit
)
with an index that looks ...
-2
votes
0answers
48 views
Unexpected index scan against partitioned table [closed]
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
3
votes
0answers
31 views
custom population of full text index
Is there any way to populate a full text search index without storing the backing information in the database?
I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
6
votes
1answer
88 views
Dropping Hypothetical Indexes
In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.
I have a database that is ...
4
votes
2answers
96 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 ...
7
votes
4answers
620 views
Index Seek vs Index Scan
Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.
What is the difference between and index seek and an index ...
0
votes
0answers
35 views
How to make continues cluster?
I have a report table with the following index:
providerid, date
The table is around 30M records and it grows about 100K rows per day.
I want to use in the index above as a cluster, but as I ...
-3
votes
0answers
32 views
How do I add a index column to a database with non unique columns [closed]
I have a database with non unique data in columns, How do I add a index column or add a primairy key column?
0
votes
1answer
54 views
Can I use the database and query it while full-text indexing?
I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
1
vote
2answers
102 views
SQL Server clustered index high fragmentation
I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.
Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
0
votes
1answer
55 views
Two identical MySQL tables, one not using indexes
I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data.
Schema of one table
CREATE TABLE `measuresHistory` (
...
0
votes
0answers
10 views
Fine grained (milliseconds) temporal indexes in neo4j
What are the best practices for modeling fine grained timelines in neo4j? If I were to use milliseconds as the grain in the pattern described here and here would it prove somehow problematic and ...
6
votes
1answer
221 views
MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`
I know very little about database administration but I have to deal with some very large tables on my site.
This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
3
votes
1answer
60 views
How can I get this query to use it's index?
Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime).
As I don't really know what I'm doing I tried adding indexes to all columns in ...
5
votes
2answers
189 views
Why disabling a clustered index makes the table inaccessible?
When an index is disabled, the definition remains in the system catalog but is no longer
used.
SQL Server does not maintain the index (as data in the table changes), and the index
cannot be used to ...
0
votes
1answer
41 views
Index on a query with order
I have the following table with >1M rows:
CREATE TABLE `wishlist_place` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`wishlist_id` int(11) DEFAULT NULL,
`place_id` int(11) DEFAULT NULL,
`city_id` ...
1
vote
1answer
86 views
Varchar index - will hashing value make it faster?
I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
1
vote
2answers
176 views
Auto-generate scripts to create tables, indexes, etc
In SSMS, we can right click a database/table/index/... and select the "Script As" option to generate a drop and create script.
Is there a way to automate this script generation and drop the scripts ...
0
votes
2answers
114 views
Sync Indexes between two tables
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.
I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
4
votes
1answer
151 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 ...
2
votes
0answers
105 views
Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?
I have an existing table:
CREATE TABLE dbo.ProofDetails
(
ProofDetailsID int NOT NULL
CONSTRAINT PK_ProofDetails
PRIMARY KEY CLUSTERED IDENTITY(1,1),
ProofID int NULL,
...
2
votes
1answer
57 views
Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds
We have a number of tables (~1M records) that have a column on them defined as: [GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with newid(). We use this ID for synchronizing data ...
0
votes
0answers
27 views
MySQL (v5.6.11) InnoDB 5.6.11 restoring indexes during table copy
When i see the profile for my optimize table query here is what i see for the InnoDB storage engine 5.6.11:
+----------------------+------------+
| Status | Duration |
...
0
votes
2answers
139 views
How do you correct a corrupted index in Oracle?
I have this ORA error in our logs:
Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: ...
0
votes
1answer
62 views
How do you correct a corrupted index in Oracle? [duplicate]
I have this ORA error in our logs:
Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921)
ORA-01110: data file 8: ...
0
votes
0answers
49 views
one vs two column index difference when doing JOIN query?
Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query :
SELECT * FROM alfa
JOIN beta on beta.id = ...
