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)

4
votes
1answer
155 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
34 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
26 views

Index on colum 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), ...
4
votes
0answers
59 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
votes
0answers
41 views

Need Review - Index Rebuild and Index Reorganize strategy?

I've written a block of code to design Index Reorganize and Index Rebuild strategy based on following recommendation. I'm considering only Clustered and Non-clustered index, not Heap. Does anything ...
-1
votes
0answers
27 views

Exchange Partition

I want to monthly move partitions of my current table into archive. For example, I have two tables with primary key constraint: create table tableSource( id number, create_date timestamp ...
1
vote
2answers
49 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
80 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
41 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
25 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
16 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 ...
4
votes
2answers
80 views
+50

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
23 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
35 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
56 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
40 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
62 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
19 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
31 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
160 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
22 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
96 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
17 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
67 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
45 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
26 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
25 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
59 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
25 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
31 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
40 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
54 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
68 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 ...
1
vote
2answers
45 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
45 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 ...
2
votes
2answers
89 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
0answers
47 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
31 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
21 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 ...
0
votes
0answers
18 views

Trigger copies history table: Will removing index lock options speed up the process?

I have a trigger applied to a table to write inserts and updates to a history table. That history table can grow pretty fast, and we want to see, if we can speed up insert operation a bit. The values ...
0
votes
0answers
26 views

Postgresql indexing for IS NULL

I have two similar queries, whose only difference (in the last two lines) is that one uses IS NULL while the other compares defined values. Query #1 takes only a few minutes: UPDATE ...
3
votes
1answer
88 views

CREATE NONCLUSTERED INDEX ONLINE, DROP EXISTING

I want to add three included columns to an existing non-clustered index. With that I'm solving performance issues in the database. Here is some information about the index: Rows: 112 Millions Disk ...
0
votes
0answers
29 views

Indexing Multiple Possibly Missing Columns

I'm working with a particularly large database, and am not particularly in tune with some of the intricacies of RDBMS's as I work primarily in PHP/WP and with smaller databases. Either way, I have ...
1
vote
1answer
47 views

Column store index on a large fact table

I have seen column store index and also tested it. I have no doubt admiring that it is lightning fast. But every tutorial and blogs I have considered, says about its limitation "table will become read ...
0
votes
1answer
14 views

What does MySQL “USE INDEX()” without any arguments do?

Does the USE INDEX() have any effect here? SELECT `posts`.`id` FROM posts USE INDEX() where `posts`.`size` > 1024; (I have it presently in some SQL because of the way the query is ...
0
votes
0answers
15 views

MySQL ignores ZWNJ character in string comparison

If you add a unique index to a char columns you can not add two string that differ only in having ZWNJ characters (e.g. 'فا' and 'ف‌ا'). However I need to store both of them. How can I overcome to ...
1
vote
1answer
77 views

Help on deciding how to pick Correct Index

I would like to ask for some help to create correct indexes for this query: SELECT DISTINCT D.TypeID FROM dbo.Sizes AS S WHERE EXISTS ( SELECT 1 FROM #PermissionsTable AS PT ...
0
votes
1answer
42 views

Index Strategy for MySQL Query that matches on one column and between two others

I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorised into groups. The events have a start and end ...
2
votes
0answers
31 views

How long does the final phase of an online index creation take?

In reading the whitepaper: Online Indexing Operations in SQL Server 2005 it says: The online index build process can be roughly divided into three phases: Preparation Build Final ...
10
votes
1answer
148 views

How to optimize a query so that it seeks on one index first, and then another index after that

I have two sets of earth measurements from satellite data, each with time fields (mjd for mean julian date) and geography positions (GeoPoint, spacial) and I'm looking for coincidences between the two ...