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
1answer
31 views

Rename PK column names when they are FK in another tables

I need to change the names on PK columns and that has relations with another tables meaning they are FK also so this is what I am doing: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET ...
6
votes
2answers
172 views

Optimizing WHERE condition for TIMESTAMP field in MySQL SELECT statement

I am working on a schema for an analytics system which tracks usage times, and there is a need to see total usage time in a certain date range. To give a simple example, this type of query would be ...
1
vote
1answer
39 views

Using a GIN index on an integer array to model sets of tags

In PostgreSQL, what are the pros and cons of using a GIN index on a column of type integer[] to model sets of tags, instead of doing a relation table? I am trying to design a simple data model with a ...
3
votes
1answer
100 views

To have PostgreSQL-like Partial Index in MySQL 5.5

I have big data where I only select a small interval of data at a time such that the selection is always in a sequence. I am trying to implement PostgreSQL like Partial index in MySQL which is ...
2
votes
1answer
68 views

Multiple indices on a table

I have run SQL Profiler and I can see that I'm "missing" some indexes (non-clustered) on a table. How is it best to handle indexes that can be a "subset" of another index? Index 1 UserId, ...
2
votes
2answers
26 views

Indexing order by columns

Based on the query below it was suggested that I create an index on the column in the order by clause. My understanding is the sorting is done on the result set and not the table. The person ...
0
votes
1answer
51 views

How to manage RAM consumption in MongoDB?

I am using MongoDB. I have a hardware device which continuously submits data to the database every 10 seconds for 4 hours a day. The data includes latitude, longitude, and other basic information of ...
1
vote
2answers
114 views

Add UNIQUE index fails with duplicate entry error, but no duplicates found

Given table: CREATE TABLE mytable ( field_a CHAR(15) NOT NULL DEFAULT '', field_b MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_d SMALLINT ...
5
votes
2answers
145 views

“Recheck Cond:” line in query plans with a bitmap index scan

This is a spin-off from comments to the previous question: Postgres 9.4.4 query takes forever Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query ...
2
votes
2answers
82 views

Fulltext search using multiple indexes

I have a table with several columns, say a, b, c, d that should be searchable. The problem arises when I need to search a, b, c separately from d (and vise-versa). AFAIK, there's no way to achieve ...
1
vote
1answer
68 views

Oracle - Using index on unique constraint, what the deal?

Using Druid to generate SQL script for Oracle, I have seen a difference between the version 3.10 and 3.13 and I would like to understand what going on. SQL generated by Druid 3.10 : CREATE TABLE ...
5
votes
3answers
260 views

Postgres is performing sequential scan instead of index scan

I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
16
votes
6answers
3k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
4
votes
2answers
223 views

How to speed up ORDER BY sorting when using GIN index in PostgreSQL?

I have a table like this: CREATE TABLE products ( id serial PRIMARY KEY, category_ids integer[], published boolean NOT NULL, score integer NOT NULL, title varchar NOT NULL); A product ...
0
votes
1answer
48 views

How to create index on long data type field

I have a MySQL table with a long data type field: `create_date` long NOT NULL, I want to index it: CREATE INDEX ix_logs_create_date ON `logs` (create_date) USING BTREE; But I encounter the ...
1
vote
1answer
63 views

Index on column with data type citext not used

In PostgreSQL 9.4, with following schema: CREATE TABLE people ( id INTEGER PRIMARY KEY, name TEXT, junk CHAR(1000) ); INSERT INTO people(id, name) SELECT generate_series(1,100000), ...
5
votes
0answers
71 views

Which to trust?

We are troubleshooting a long-running issue with a vendor. Their software has a tendency to freeze up and stop working once or twice a week causing major disruptions to our operation. They have been ...
1
vote
2answers
67 views

How is MySQL using an index when doing a case sensitive comparison?

I noticed that Ruby on Rails (ActiveRecord) was using the BINARY operator for a case sensitive comparison. Yet, when I look at the EXPLAIN for this, MySQL can still use the appropriate index for a ...
1
vote
2answers
184 views

Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision ...
0
votes
0answers
75 views

How can i add index in MYSQL stored procedure?

I can not use alter table () on my stored procedure i don't have a permission to do that in the big database data_pro. How can i use index in my stored procedure for my 2 fields in where condition? ...
0
votes
2answers
34 views

List full text indexes in Oracle database

Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index. How can I do something similar using Oracle? Is it possible?
1
vote
1answer
21 views

Order of DROP_EXISTING and ONLINE for CREATE INDEX

If I call a CREATE NONCLUSTERED INDEX using DROP_EXISTING = ON and ONLINE = ON what order are things done it? What I want is: New index is made in the TempDB Existing Index is dropped New index ...
6
votes
2answers
135 views

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
2
votes
1answer
27 views

Problems with sqlcmd -q and quotations marks

I have a SQL Server 2008 R2 Express edition. Because it is only an Express edition I don't have a SQL Server Agent for automation of tasks. So I tried to do something with command line program ...
1
vote
0answers
40 views

Mariadb support rtree index on innodb soon?

I was looking through mariadb bug list online (https://mariadb.atlassian.net/secure/RapidBoard.jspa?rapidView=24&projectKey=MDEV), but I am unfamiliar with JIRA so I am not sure exactly what I am ...
1
vote
1answer
76 views

Why isn't Postgres doing an index-only scan?

I am using Postgres 9.4. This is my table: id | integer | not null default nextval('frontend_prescription_id_seq'::regclass) presentation_code | character varying(15) ...
0
votes
1answer
63 views

MYSQL fulltext - What's the right way to get the relevance from one column?

I want to make one column more valuable than others like it's been done in query bellow.This example shows that only one fulltext index is needed And this answersuggests that three fulltext indexes ...
2
votes
1answer
76 views

Best index for similarity function

So I have this table with 6.2 millions records and I have to perform search queries with similarity for one for the column. The queries can be: SELECT "lca_test".* FROM "lca_test" WHERE ...
0
votes
0answers
46 views

In Oracle, when you update an SDO_GEOMETRY column with the same value is the MDSYS SPATIAL_INDEX updated?

Because generating and updating a spatial field is relatively expensive we want to avoid it when possible. Here is the relevant bit of our schema: +---------+---------------+ |LATITUDE ...
0
votes
2answers
33 views

Mysql query is using some other index

We have 2 tables: CREATE TABLE `allcallers` ( `id` varchar(50) NOT NULL DEFAULT '', `company_id` varchar(50) NOT NULL, `clid` varchar(30) NOT NULL, `clid_raw` varchar(50) NOT NULL, `uid` ...
2
votes
2answers
167 views

Index Seek on SQL Server

Sorry folks if this sounds obvious. But does index seek always work on index pages not data pages? If so, which I think it is, it can only be used on its own if the output list are the same as index ...
1
vote
2answers
43 views

DB2 10.5 - When inserting, the database is trying to reuse index keys on GENERATE ALWAYS

I am trying to insert a row into a database table which has a GENERATED ALWAYS primary key column. The table has over 1000 rows, and the primary keys start at 1. When I try to do an insert, the error ...
3
votes
2answers
109 views

Online index rebuild snapshot location and impact of filling index drive

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. My question is where does the snapshot index ...
0
votes
1answer
45 views

What data structure is used for MongoDB 3.x geospatial indexes?

For the MongoDB 3.x geospatial features what structure is used for the indexes? B-tree? R-tree? X-tree? I can't find any information about the structures in their documentation
2
votes
1answer
115 views

Have foreign key constraint and index key for the same column(s)

I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My ...
0
votes
1answer
51 views

How can we drop a constraint without losing the automatically created index?

How can we drop a constraint without losing the automatically created index? If it's possible using a single query.
0
votes
1answer
33 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
1
vote
1answer
29 views

How to index an property object in MongoDb?

i've the following sample document: { "_id" : ObjectId("556e177ca43da29e040041a7"), "user_id" : NumberLong(24), "request_datetime" : ISODate("2015-06-02T15:52:12.000-05:00"), ...
3
votes
1answer
79 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
1
vote
1answer
63 views

DBCC DBREINDEX in MS SQL Server 2008

We have done DBCC DBREINDEX for our production database which size is about 300 gb, it was running for 12+ hours, but i found that DBCC DBREINDEX is obsolete and recommended approach is using ALTER ...
1
vote
1answer
32 views

Sql Server 2008 : What is the cost of accessing an index?

I've just read this interesting blog post about selectivity notion. In the section 'When is better to not use a database index?', the author states : Accessing the index takes time, and consumes ...
1
vote
1answer
50 views

Why is MariaDB 10 refusing to use my index on a date field?

I have the following table definition: CREATE TABLE `bets` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `id_mp` bigint(20) NOT NULL DEFAULT '0', `coin` char(3) COLLATE utf8_unicode_ci NOT NULL, ...
3
votes
1answer
98 views

How to maintain a global index on a large table undergoing regular partition purges?

Problem I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate ...
0
votes
1answer
102 views

Estimation of creating index in SQL Server

We create new index on our production database, with a few fields, our database size is around 300GB and it works for one hour and do not finished yet. How can you estimate this operation? If we ...
4
votes
3answers
85 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
2
votes
1answer
49 views

Index fragementation ? is it unavoidable

This is follow up question to @martin smith excellent answer http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean After that i have watched MCM video of ...
4
votes
3answers
120 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
1
vote
1answer
62 views

Postgres not using the index even when rows returned is 5% of the table

I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | ...
0
votes
0answers
67 views

Is there some tricks to better index a URL column in postgres?

I'm working with a table that needs fast retrieve rows by its url column. But they all will be starting with http://www.: http://www.google.com http://www.amazon.com http://www.stackoverflow.com I ...
1
vote
0answers
22 views

Calculate Num_Index_Pages for sizing design

I don't understand Step2 #8 Calculate the number of number of non-leaf pages in the index. I am not sure I understand the "Level". I have Num_Leaf_Pages=508 & Index_Rows_Per_Page=476 I don't ...