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
0answers
26 views

Why is this MySQL query performing slowly despite correct indexing? [duplicate]

I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation. So my table A looks like this: CREATE TABLE `A` ( `id_b` int(10) DEFAULT NULL, `id_c` int(12) ...
2
votes
1answer
79 views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
2
votes
2answers
215 views

DISTINCT not using an index [duplicate]

I have table (2139868 rows): COLUMN1 NUMBER(20,0) COLUMN2 XMLTYPE COLUMN3 XMLTYPE COLUMN4 VARCHAR2(50 BYTE) COLUMN5 VARCHAR2(50 BYTE) COLUMN6 VARCHAR2(50 BYTE) COLUMN7 VARCHAR2(50 BYTE) COLUMN8 ...
0
votes
1answer
42 views

Can't force mysql to use index

There are many similar questions about force index but mine is something different because i successfully use index at local dev server but can't on production server. I have a table with more than ...
-2
votes
2answers
67 views

Index is not used with same Query on two Servers [closed]

I have two notebooks with same specs (same model, only ram is 24GB/32GB, same ssd) and exact same database and same sql server version. SQLIO is 3300 and 3600. Now I run a query on one notebook and ...
1
vote
0answers
98 views

What is a loose index scan?

I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan" What exactly does this mean? Is it related to the underlying or the implementation of the ...
0
votes
1answer
63 views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
2
votes
1answer
197 views

Postgresql not using GIN trigram index when performing non-ASCII LIKE query?

Steps to reproduce Create database CREATE DATABASE citiesdb WITH OWNER = citiesowner ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' ...
2
votes
3answers
253 views

Listing indexes and constraints

I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me. The database table I am looking at has ...
1
vote
2answers
202 views

Why is MySQL not using the index with the higher cardinality?

I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
5
votes
1answer
91 views

SMS Suggesting covering all columns in an index?

I'm still a bit newer to indexes and database works, so I may very well be missing something obvious. SMS suggested adding a covered index to one of my tables that Indexes on the column I believe ...
8
votes
1answer
591 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
1
vote
1answer
43 views

Index against a subset of rows?

Is it possible to create an index against a subset of rows fulfilling a certain condition? If it is possible how does one ensure the index is used?
1
vote
0answers
291 views

Error with Solr DataImporter using Mongodb

I've configured my environment and I've downloading Solr 4.7 The idea is make an indexing my mongodb database. For this I've installed the mongo-connector like appear here: ...
0
votes
2answers
82 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

I just need advice on how I am going to optimize my database. Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN I have that format of tables on my database. Each ...
2
votes
1answer
64 views

What happens when CREATE INDEX gets interrupted (manually or accidentally)?

It takes hours or even days to create an index for a table of millions of rows. What happens to the database / data when CREATE INDEX gets interrupted? Such as when I manually used Ctrl-C or when ...
17
votes
2answers
661 views

SQL INDEX - how it works?

My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was working with databases. I have read few books and I have ...
1
vote
1answer
74 views

SQL INDEX - how it works? [duplicate]

See this question here: Database Administrators meta My knowledge of databases and SQL is based in most on university classes. Anyhow, I spent few monts (almost a year) in a company, where I was ...
6
votes
2answers
561 views

Improve performance of COUNT/GROUP-BY in large PostgresSQL table?

I am running PostgresSQL 9.2 and have a 12 column relation with about 6,700,000 rows. It contains nodes in a 3D space, each one referencing a user (who created it). To query which user has created how ...
0
votes
1answer
53 views

Last used index from specific table

how can i take last used index from a specific table and add a +1 to it? database login, table cars I think i need to add it here #### $sql = 'INSERT INTO '.$this->options['db_table'] ...
0
votes
1answer
78 views

Why is this query slow, it seems simple

I have a very typical table of user information for my application, with an average set of columns (ints, varchars, etc). In this table are two varchar(100) fields: emailAddress and emailAddress2. ...
0
votes
1answer
65 views

How to index WHERE (start_date >= '2013-12-15')

I have a table named items, and inside the table there is a some_date column, with date as the datatype. I was wondering how can I index the some_date column with PostgreSQL SELECT "items".* FROM ...
1
vote
1answer
145 views

GETDATE() uniqueness when used on INSERT

Recently I was reading this blog post: http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx which contains this section: Picking a Clustered Index There are a couple of ...
3
votes
0answers
94 views

Why would function based indices I’ve created lower the cost but now show up in the explain plan breakdown?

Sorry for the terrible column/table names but since this is for a work project I wanted to insure it was ok to ask. I was just hoping to at least learn why I'm not seeing my functional indices being ...
2
votes
1answer
62 views

Clustered index additional storage

Reading some books I found that clustered index not require additional disk space for storing index data, because leaf level of clustered index are data itself. But, as clustered index is organized ...
0
votes
3answers
177 views

How MongoDB indexes differs from MySQL indexes?

I am a newbie to MongoDB. I know how Indexes works and what it's importance in MySQL. Are MongoDB indexes are the same as MySQL ?
1
vote
0answers
41 views

Database tuning and possibly unexpected results

(This is cross-posted from stackoverflow; I initially put it in the wrong place.) So I have a database that's working a bit slower than I would expect and I'd like to describe what I'm doing so ...
1
vote
0answers
67 views

How to take advantage of multiple spatial indices?

All, I'm building a view which uses a spatial intersect (STIntersect in MSSQL 2012) between points (student addresses with address information) and polygons (school boundary polygons with school ...
0
votes
0answers
162 views

index usages on table with recursive relationship

we are working on a project that uses jpa and oracle. In this project we have a table with a recursive relation: Create table alarm ( id varchar2(200), ... alarmcode varchar2(50), parent_id ...
2
votes
2answers
125 views

SQL Server 2008 R2 Missing Index

I am performing index maintenance on a database. I have run SQL script that uses DMVs to provides a list of indexes SQL Server believes should be created. I am not creating indexes just because the ...
0
votes
2answers
58 views

MySQL Index for searching for NULL values in text fields

I need to do the simple query SELECT id FROM table WHERE blob_field IS NULL. blob_field is a longblob. If I create an index on blob_field with prefix length 1, will this be sufficient to use for ...
3
votes
2answers
123 views

Compound Index on 43 Million PostgreSQL table

This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order) Rather than overload that question, I figure I can sharpen and limit my ...
1
vote
1answer
131 views

Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL. The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
3
votes
2answers
72 views

SQL Server NULL Index

I am new here and have a little question. In a customer environment after checking his index fragmentation we see some "NULL" named index that we couldn't rebuilt or reorganize. This was a SQL ...
10
votes
3answers
636 views

Indexes for SQL query with WHERE condition and GROUP BY

I am trying to determine which indexes to use for an SQL query with a WHERE condition and a GROUP BY which is currently running very slow. My query: SELECT group_id FROM counter WHERE ts between ...
1
vote
1answer
136 views

Is indexing binary?

The scenario: When a user updates some information - the correct row must be found and updated. I'm debating between two options: Let the server search through all of the rows (-slow). But be ...
0
votes
0answers
28 views

SQL Server Rebuild and Fragmentation

After running a SQL rebuild the indexes are still fragmented - some over 50%. What else can we do to reduce the fragmentation? Do we need to run the rebuild multiple times? Thanks for the help
2
votes
1answer
80 views

indexes with unspecific columns

I have a third party database in which a large number of the tables have a 'companyID' column, and most of the queries against those tables include companyID = @companyID in the where clause. [EDIT: ...
0
votes
3answers
42 views

MYSQL Indexes when looking up on alternating sets of columns

I have a table that looks like: CREATE TABLE `connections` ( `connection_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `service_id` smallint(5) unsigned DEFAULT NULL, `parent_id` bigint(20) ...
1
vote
2answers
77 views

Should I create an index for non key columns?

I have a table in a PostGreSql database defined as following: CREATE TABLE public."MATCH"( "ITEM_A_ID" bigint DEFAULT 0, "ITEM_B_ID" bigint DEFAULT 0, "OWNER_A_ID" bigint DEFAULT 0, ...
1
vote
2answers
64 views

index for OR operator with different columns

What would be best index for this kind of query: -- id is the PK create table test3(id number,name varchar2(30) not null,lastname varchar2(30) not null); select id from test3 where id=:1 and ...
2
votes
2answers
180 views

Postgres point() index not being used

\d aggregate Materialized view "public.aggregate" Column | Type | Modifiers ------------------+-----------------------------+----------- ...
4
votes
2answers
81 views

Spatial index not used

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting ...
0
votes
1answer
184 views

PostgreSQL - Return unique combinations of columns based on where clause

This is quite a long question, please bear with me. So I'd like to first explain I have a database of firewall logs created using the following command: CREATE TABLE firewall_logs_mapped ( ...
3
votes
4answers
733 views

indexing on multiple nvarchar(max) columns

I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading. What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed ...
4
votes
1answer
548 views

Deleting Rows is Increasing Index Size

We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table ...
2
votes
2answers
235 views

Why does MySQL fail to use an index effectively when LIMIT offset is large?

I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec: SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5; But querying ...
0
votes
1answer
144 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
0
votes
1answer
62 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
5
votes
2answers
131 views

Why is an entire index that is infrequently used stored in the buffer pool?

I have been looking at the memory usage of a server and noticed something odd when drilling into the details. One index is eating up ~15GB of the buffer pool. That happens to be the size of the entire ...