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)

1
vote
4answers
734 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
368 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), ...
7
votes
1answer
965 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
147 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
321 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
64 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
98 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 ...
6
votes
2answers
5k 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') && ...
2
votes
1answer
363 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
222 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 ...
3
votes
0answers
159 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 ...
7
votes
3answers
1k 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
479 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 ...
15
votes
4answers
13k 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 ...
2
votes
0answers
56 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 ...
0
votes
1answer
100 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?
2
votes
2answers
3k 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
117 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` ( ...
7
votes
1answer
3k 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
86 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 ...
7
votes
1answer
3k 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
56 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
2k 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
3k 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
472 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
514 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 ...
5
votes
1answer
3k 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
605 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 ...
1
vote
2answers
3k 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
201 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: ...
1
vote
2answers
2k views

Why PostreSQL 9.2 does not use index scan if it can?

My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan. Here is my table. The index I am talking about is name_left_prefix cwu=# \d web_city; ...
0
votes
2answers
341 views

Is there slowdown inserting into an InnoDB table that has no index set?

I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such. Those tables only contain a few thousand rows. Would it be faster to INSERT data ...
1
vote
0answers
37 views

parenthetic grouping of join clauses

What is the difference between these joins? a left join b left join c (a left join b) left join c a left join (b left join c) Does the grouping only affect the order of the joins? Will the query ...
1
vote
2answers
355 views

Clustered vs Nonclustered Index

My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as: SELECT * FROM ...
3
votes
1answer
435 views

mysql: need help to optimize my query/table

I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
3
votes
2answers
299 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 ...
2
votes
1answer
142 views

Validate Primary Key and Index Selection

I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this ...
2
votes
1answer
215 views

In MySQL, does the order of the columns in a WHERE clause affect query performance,why? [closed]

I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) ...
2
votes
1answer
62 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 ...
4
votes
2answers
3k views

Suspended in index rebuild

In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job. In monitoring SQL task: state = ...
5
votes
1answer
298 views

When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?

Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed) I have a table that's got 19 ...
1
vote
1answer
275 views

SQL Index order and performance based on cardinality and data

I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting ...
10
votes
3answers
12k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
7
votes
3answers
782 views

Should I separate frequently updated columns?

I have a users table that contains users' information and a column named credits that is frequently updated. On index page I'm showing list of users with basic user information but I don't need ...
4
votes
1answer
277 views

How can I alter the fill factor of an index outside of an ALTER INDEX command?

I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB ...
4
votes
2answers
2k 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 ...
6
votes
2answers
2k views

Bitmask Flags with Lookup Tables Clarification

I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
1
vote
1answer
103 views

Big jump in search time for Postgres Index query for results with high selectivity

I am doing some performance comparison of databases and lucene for full-text searching. So I use Postgres to create an Index for the data to search: CREATE INDEX bodies_index ON bodies USING gin ...
4
votes
1answer
3k views

Why is a hash match operator in this very basic query

I'm beginning to learn some about looking at execution plans and making queries more efficient Consider these two basic queries select distinct pat_id, drug_class, drug_name from rx select pat_id, ...
5
votes
2answers
732 views

Recreate Indexes on 1 billion record table

I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...