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

Get missing index statistics in SQL Server 2014 [duplicate]

For example consider a database that has many SP and many query execute on it using ORMs. It's very time consuming that check every SP or profile all queries that ORMs generate for performance and ...
2
votes
1answer
17 views

Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic?

I have a UNIQUE constraint of a number of fields on a table. Image that I want to change the fields in this constraint (remove one field). Normally I would do something like: DROP INDEX unique_name ...
0
votes
0answers
17 views

Auto increment id column vs index VARCHAR columns

I'm wondering the differences or comparisons of having an Auto Increment int Id column or an indexed VARCHAR(32) column. Which one would be faster if I had a second table that references the first ...
3
votes
1answer
25 views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
3
votes
1answer
69 views

Foreign Key Constraint Set Back to Trusted

We have some production tables with numerous constraints that are not trusted at the moment. We're having them checked/trusted so they can be used again. We know that the optimizer has not been ...
1
vote
0answers
32 views
+250

MySQL Online DDL Index Creation

I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is ...
12
votes
1answer
253 views

Clustered index not used in a delete statement

I have a SQL Server table defined as follow CREATE TABLE [dbo].[Production_Detail] ( [Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]), [Meta_Data_ID] INT NOT NULL ...
1
vote
1answer
46 views

SELECT count(*) from a materialized view with unique index is slow

I have a materialized view with a unique index (on the id column) and both of these queries are slow (they scan the whole table): SELECT count(DISTINCT a.id) from the_view a; SELECT count(*) from ...
95
votes
6answers
31k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
5
votes
1answer
882 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
4
votes
0answers
71 views

Why different datetimes freezes a query based on indexes?

I have a table with a billion records. It has these columns: Id bigint, MobileNumber varchar(100), Date datetime, Message nvarchar(400) And it has non-clustered indexes on MobileNumber and Date ...
0
votes
1answer
146 views

Does remove document automatically remove the index that reference this document?

I am wondering if removing the document will automatically update the index if this document is referenced?
0
votes
0answers
30 views

Does having a .ldf for a index file cause issues?

Interesting find today, I have a file group that is used for indexing. It has one file that has a .ldf extension, which as you know for SQL Server, is the extension for the transaction log files. My ...
25
votes
3answers
29k views

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an ...
0
votes
2answers
40 views

Query to retrieve index details for a particular column in all tables

There is a column created_at in most of tables. I would like to get the details if that field exists in the table and it is not indexed. Is there any thing can be done with the information_schema to ...
1
vote
3answers
5k 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: '/data/app/oracle/oradata/MYSRVR/...
3
votes
2answers
173 views

Partitioning a large table has not improved performance, why?

In SQL Server 2014 I have partitioned one of my large tables weekly and defined a sliding window scenario to switch the oldest week's data to the archive DB and create a new partition for the next ...
1
vote
2answers
27 views

MySQL CREATE INDEX slowing down

My analytics table has 190M rows, about 150GB ins size. Storage engine is MyISAM with key-buffer-size=16G myisam_sort_buffer_size=2G myisam-max-sort-file-size=200G. Machine has 32GB memory. While ...
0
votes
1answer
155 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
1k views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
1
vote
1answer
184 views

Index to fix this query MySQL

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the ...
4
votes
2answers
437 views

Clustered vs Nonclustered indexes, similar to arrays vs linked lists?

I'm just starting out as a DBA, and have recently been interested in indexes and how they speed up queries. I was reading about indexes here: https://msdn.microsoft.com/en-us/library/ms190457.aspx ...
1
vote
0answers
30 views

Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for ...
1
vote
4answers
247 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
2
votes
2answers
2k views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
3
votes
2answers
208 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', album....
0
votes
1answer
61 views

Building MySQL InnoDB clustered indexes

Assuming that you have a table like CREATE TABLE `t1` ( `search_id` int(11) NOT NULL, `provider_id` bigint(20) unsigned NOT NULL, `status` text NOT NULL, `imported_at` int(11) NOT ...
1
vote
0answers
17 views

Primary key index not used when using inner join with jsonb_array_elements subquery on Postgresql 9.5

I have two tables: hub and huboperator hub table is defined as CREATE TABLE public.hub ( parent_hub_id integer, ancestors jsonb, descendants jsonb, name character varying(1024), id integer ...
1
vote
0answers
46 views

Clustered Columnstore Index on Table with Foreign key

Using SQL Server 2014, I am trying to create a clustered columnstore index on a table which is referenced by a foreign key. This is the syntax I am using CREATE CLUSTERED COLUMNSTORE INDEX ...
0
votes
2answers
125 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?
0
votes
3answers
50 views

Why full index scan over full table scan?

I have a query that pulls from small tables in MySQL: SELECT C.cid, C.email, R.flight_number FROM customer C INNER JOIN reservation R ON C.cid = R.cid WHERE C.email LIKE '%d.com'; There exists an ...
3
votes
1answer
54 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
0
votes
1answer
56 views

Effect of Reorganizing and Rebuilding an index [closed]

What are the effects of Index Rebuilding and Reorganizing on clustered and non-clustered indexes. How does these 2 operations affect the logical storage and physical storage of data in Hard disk?
0
votes
2answers
277 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
246 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not all)....
0
votes
1answer
32 views

Mysql read only engine type and indexes

I have a mysql table. This table has only three columns. Two are varchar (5),and one is varchar (32). There will only be one query run against table. Select (column 1),(column 2) from mytable where ...
8
votes
1answer
1k views

MSG 666 when running an insert query on 80M-row indexed table

Strangely, my stored procedure started to receive Msg 666 for some input data. Stored procedure fails on the last step when it tries to insert a row into a table with the following structure: ...
1
vote
1answer
35 views

Count approximation with where clause

I have the following problem, we have a table of facilities for apartments that looks something like this: I would like to perform the following query SELECT crawled_name, count(f.id) AS count ...
1
vote
2answers
41 views

Dynamic script to Drop Indices of a specific column

I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column). In my script I have ...
3
votes
2answers
104 views

100% index fragmentation

I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them. Rebuild ran for about 20 minutes. Then I found a query to see how my ...
0
votes
1answer
24 views

Postgresql: Storing a table index on a separate disk to the data

I have development a postgresql 9.5 database which I originally set up on an SSD. However, as this project will grow to ~200TB (and likely more), I have moved my current test of ~200GB on to a HDD, ...
0
votes
1answer
21 views

MongoDB ixscan seems to be scanning more objects than expected

I'm using MongoDB 2.6.12 and a collection has the following index {a: 1, d: -1}. In case the types matter a is a Number and d is a Timestamp. From what I can see in the code we are limiting this ...
0
votes
1answer
23 views

potgresql foreign key index unused for some columns on one specific server

I have a table publication, with two columns type_id and sub_type_id (there are, of course, more columns, but they are not important) on a server, all containing int8 foreign keys to tables type and ...
3
votes
2answers
166 views

Clarification on Clustered index scan and Clustered Index Seek

I have created one table with 80000 records with Cluster Index on CustomerID column. When I queried the following statements the execution plan behaves in differently. Can anyone clarify the same. ...
1
vote
1answer
61 views

Using indexes in ORDER BY MySQL

Here i have table with indexes and more than 700000 entries. CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` char(255) COLLATE utf8_unicode_ci NOT NULL, `cat_id` int(...
1
vote
0answers
25 views

Add index to mysql table with ndbcluster engine

I'm having trouble while adding index on mysql table with ndbcluster storage engine below is the error . [mysqlcluster ver. 7.4 GA] mysql> alter table `sks_staff_office` add index `pid_index` (pid)...
3
votes
1answer
59 views

Increase in IO Wait after adding indexes with postgres

I've recently been put in charge of a system that has been fairly untended for a period of time. The majority of activity is via queued worker jobs based off of data feeds from other systems/data ...