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
33 views

Speed up COUNT(*) when using an index

I have a table similar to this (simplified): CREATE TABLE books ( id INT AUTO_INCREMENT, category INT NOT NULL, PRIMARY KEY (id), KEY (category) ); This table has over 10,000,000 ...
4
votes
1answer
72 views

When to use sort_in_tempdb when rebuilding indexes?

We are debating whether to use the SORT_IN_TEMPDB option for our DW tables. My understanding is that there are more writes when using this option, although they are more sequential. We have a SAN ...
1
vote
3answers
56 views

Intentionally corrupting an index in PostgreSQL

I need to know if there is any method for intentionally corrupting an index in PostgreSQL. I have been searching for it for a long time but never seen anyone mentioning such an act.
2
votes
1answer
127 views

Dropped Index during Database Engine Tuning Advisor

I was analyzing a query using "Database Engine Tuning Advisor". During this time the database started to get blocking, long running queries and ended by restarted the instance. When I checked after ...
2
votes
1answer
45 views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
3
votes
1answer
33 views

MySQL query WHERE IN + ORDER BY, which EXPLAIN is better and how to avoid filesort?

This is my table definition, without any special index for now: CREATE TABLE `filter` ( `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `category_id` varchar(32) COLLATE ...
0
votes
1answer
46 views

Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?

I am using PostgreSQL 9.4.4. I have a query like this: SELECT COUNT(*) FROM A,B WHERE A.a = B.b a and b are the Primary Keys of tables A and B, so there are B-indexes on a & b By default, ...
2
votes
1answer
29 views

Non clustered index for non-evenly distributed data in large SQL table

I have the following table containing 2.000.000.000 rows: CREATE TABLE [VeryLargeTable] ( [dataCol1] [NVARCHAR(MAX)] , [dataCol2] [NVARCHAR(MAX)] , [Manufacturer] [NVARCHAR(10)] , ...
0
votes
1answer
33 views

Custom big key-value database indexing [closed]

I want to make a key/value database with millions of records. A custom-length unique UTF-8 text will be used as key 16 unique bytes will be used as value What is the best way of storing this ...
0
votes
2answers
39 views

LOB indexes corruption in Oracle

Why is it not possible to rebuild corrupt LOB indexes? So if my LOB index is corrupted, how to recover it?
3
votes
2answers
102 views

SQL server tools to identify query optimisations and missing indexes [closed]

I have a broad question regarding SQL Server that was given to me on a quiz and I am not sure what the answer is as I do not have access to SQL Server at home. My question is how can I utilise SQL ...
3
votes
1answer
31 views

Guidelines for full-text index maintenance

What guidelines should be considered for maintaining full-text indexes? Should I REBUILD or REORGANIZE the full-text catalog (see BOL)? What is a reasonable maintenance cadence? What heuristics ...
0
votes
2answers
20 views

Separate indices for the field I want to get the MAX() and the ones in the WHERE clause

My customer's database has a gigantic table (hundreds of millions of records) and a SELECT that, by cumulative use, is eating a huge chunk of processing time. Each exec takes almost a minute in ...
1
vote
0answers
25 views

Speeding Up Query - Hangs at Sending Data - Indexed Correctly

I am looking for ways to speed up the below query. I currently have indices on FIRST, LAST, UNIT_PARENT, HomeAddressLine1, HomeAddressLine2, HomeCityNAme, HomeStateAbbreviation, HomeZipCode, HomeZip4, ...
1
vote
0answers
44 views

Database fragmentation and heap SQL Server

We have critical performance issues with our production database (its around 300 gb size) We have run the following script for a few huge tables : DECLARE @db_id SMALLINT; DECLARE @object_id INT; ...
1
vote
1answer
30 views

Database index on FK with only NULL values

We have in our database an index with table cardinality over 10.000 but full key cardinality only 1. The reason for this that all the values for the column are NULL, hence cardinality 1. Does this ...
3
votes
1answer
54 views

Finding optimal indices, different query competing indices

My schema: create TABLE "logs" ( "id" serial not null default nextval('logs_id_seq'::regclass), "request" varchar(1024), "token" varchar(512) default NULL, "was_batch_request" bool ...
3
votes
0answers
35 views

type ALL in explain [closed]

I have tables like these: items +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
29 views

Which are the differences and advantages on single INDEX or compound INDEX?

I have two INDEX on a n:m relationship table as you can see on pics below: Now I can have this also: My questions: What is the advantage from last way(pic compound) over the two first? Can ...
1
vote
1answer
23 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
154 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
28 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
89 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
65 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
45 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
86 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
104 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
65 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
59 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
209 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 ...
6
votes
2answers
726 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
203 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
42 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
52 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), ...
4
votes
0answers
66 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
58 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
114 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
59 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
28 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
18 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 ...
5
votes
2answers
116 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
24 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
37 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
66 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
48 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
69 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
33 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
164 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 ...