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
22 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 ...
3
votes
1answer
62 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 ...
1
vote
0answers
54 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
1answer
33 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 ...
0
votes
1answer
66 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
3answers
70 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
2answers
119 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
40 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, ...
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 ...
4
votes
2answers
176 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
62 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
30 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)...
0
votes
1answer
33 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 ...
1
vote
1answer
47 views

MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index

It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, ...
1
vote
1answer
35 views

Perfomance of CREATE INDEX vs CREATE INDEX CONCURRENTLY in PostgreSQL

I know CREATE INDEX CONCURRENTLY is slower, but how many longer it will takes to finish in comparison with the traditional CREATE INDEX? 40% more or much more than that? I am creating an index in a ...
1
vote
1answer
51 views

Oracle index with REGEXP_REPLACE is it a good idea?

I have a table of contacts with 1 million registers and now the user wants to search in it by the telephone. Trying to avoid a fullscan I decided to create a new index in this column, but the trick ...
8
votes
1answer
316 views

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

I am trying to improve this (sub-) query being part of a larger query: select SUM(isnull(IP.Q, 0)) as Q, IP.OPID from IP inner join I on I.ID = IP.IID where ...
3
votes
1answer
60 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 ...
1
vote
1answer
18 views

What run-time configuration parameters are used for concurrent index creation

Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value. mhildreth=# show maintenance_work_mem; maintenance_work_mem ---------------------- 16MB (1 ...
0
votes
1answer
25 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 ...
1
vote
2answers
43 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 ...
1
vote
1answer
64 views

Should I remove this clustered index?

I have lots of tables designed similar as the following (slightly simplified) sample. The original table on different servers has between 50,000 and 200,000 rows (around 12,000 - 150,000 pages). ...
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
2answers
98 views

Index not used on partitioned table

We have a table similar to this one mysql> SHOW CREATE TABLE my_requests\G *************************** 1. row *************************** Table: my_requests Create Table: CREATE TABLE `...
0
votes
1answer
24 views

Different behaviour between oracle and postgres regarding alter table primary key command

In Oracle 11g, when you write alter table abc add constraint abc_pk primary key (one, two, three); Automatically an index for one, two, three is added. In PostgreSQL 9.5, when you do the same, ...
5
votes
1answer
69 views

Index creation leads to large transaction log growth

I have an approximately 1 billion row table. I then create an index as follows: CREATE NONCLUSTERED INDEX [IX_Index1] ON [dbo].[MyTable]( [CustomerID] ASC ) -- CustomerID is uniqueidentifier WITH (...
0
votes
3answers
80 views

What is the performance impact of indexing a frequently updated column?

I just want to know if I'm doing this right. I've searched google for hours already and I can't find a straight answer. I have a table with potentially a very large amount of inserts and updates. Any ...
-3
votes
1answer
30 views

Database engine disk access [closed]

Do you know what is the difference between the database engine get rows in a table without and with use of an index in a disk prespective. They way the engine access the table in disk is the same when ...
1
vote
0answers
34 views

Resolve “stuck” system process of dropping indexes

I am re-vamping some indexes, and have decided to drop 3 on a certain table. The process has been running for almost 2 hours now. The table does have quite a few million rows, but I still feel that is ...
0
votes
1answer
55 views

MongoDB 'too many indexes' problem

I've got a problem, when I got 6 fields and have no subset of fields, which my app always uses. That means that all of my queries always have different set of fields to scan. Then I read a little old ...
1
vote
2answers
73 views

Trigram index for for ILIKE patterns not working as expected

I've got a simple but slow query: SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%RYAN WER%' AND clientid = 31239 AND time_job > 1457826264 ORDER BY title LIMIT 10; Explain analyze: ...
3
votes
2answers
45 views

Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT (*) AS "count" FROM "Posts" AS "Post" WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' AND "Post"."new" = TRUE; Index: CREATE ...
2
votes
2answers
267 views

Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases. I have then passed this .trc trace file as a parameter to the database ...
5
votes
1answer
156 views

Indexes: integer vs string performance if the number of nodes in the index is the same

I hope this is the right place to ask! I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the ...
1
vote
1answer
24 views

INDEX size as bytes - PostgreSQL 9.2

Is there any way to get this query to return the index size as BYTES? SELECT relid::regclass AS table, indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid::...
7
votes
1answer
155 views

Two columns with and without index in where clause - How index works

I am executing a query on SQL Server 2008: SELECT col1 FROM table1 WHERE col2=val2 AND col3=val3 Here col2 has a non-clustered index, col1 is the PRIMARY KEY, and col3 does not have any index. ...
3
votes
3answers
161 views

Why SQL Server uses Non-Clustered but not Clustered Index?

I have a table with 145 million rows CREATE TABLE [dbo].[RFTest]( [SnapshotKey] [int] NOT NULL, [SnapshotDt] [datetime] NOT NULL, [LoanNum] [int] NOT NULL, [GLSourceSystem] [varchar](...
0
votes
0answers
30 views

SQL Server Index recomended but already there (DMVs)

I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc. Some examples are Index ON X ( Y ) and Table ...
0
votes
2answers
34 views

Index for column with two possible value

I use PostgreSQL 9.4. I have a database where a table have index in column which can contains two possible value only(gender: male/female). When I execute a query with condition on this column index ...
0
votes
0answers
26 views

pt-duplicate-key-checker - DBD::mysql::db selectall_arrayref failed: MySQL server has gone away

I run pt-duplicate-key-checker a few months ago successfully. Now I receive on the same database (Galera, RHEL7) those errors DBD::mysql::db do failed: MySQL server has gone away On CLI I can do use ...
1
vote
2answers
39 views

Slow query / Indexes creation (PostgreSQL 9.2)

I have the following Query: explain analyze SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
0
votes
1answer
58 views

Find unused indexes

I'm using the following query to find unused indexes: SELECT PSUI.indexrelid::regclass AS IndexName ,PSUI.relid::regclass AS TableName FROM pg_stat_user_indexes AS PSUI JOIN pg_index AS PI ON ...
0
votes
1answer
11 views

Missing / Orphan Indexes?

In the process of finding duplicate indexes, I've been using the following query to identify them: SELECT indrelid::regclass AS TableName ,array_agg(indexrelid::regclass) AS Indexes FROM pg_index ...
2
votes
0answers
19 views

In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation?

I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production ...
2
votes
1answer
65 views

Query with Indexes slower than without Indexes

I'm using MySQL 5.6 and my storage engine is InnoDB. I have a table with 1 million rows containing with the columns: ID (primary key) FirstName LastName foreign_key_id (foreign key, NOT NULL) ...
5
votes
1answer
91 views

Conditionally add an index to a temp table

I have a stored procedure that looks something like this: SELECT columnlist INTO #temptable FROM Table JOIN lotsofothertables WHERE severalconditions UPDATE anothertable SET column = #temptable....
1
vote
1answer
50 views

InnoDB: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better?

Say I have a table ( person_id INT AUTO_INCREMENT, birthday_timestamp BIGINT, country, state, ..., PRIMARY KEY (person_id), KEY (birthday_timestamp) ) where people are added the instant they'...
2
votes
0answers
34 views

How is Index rebuild affected by IN_ROW_DATA vs LOB DATA

I am using Ola Hallengren's scripts to maintain my databases. On a few of them there are indices that don't seem to be benefiting from the maintenance. I know that his scripts have a lower threshold ...
2
votes
1answer
89 views

Why does Cassandra recommend against creating an index on high-cardinality columns?

The Cassandra documentation states, Do not use an index in these situations: On high-cardinality columns because you then query a huge volume of records for a small number of results. See ...
-1
votes
1answer
50 views

Why and how to index a MySQL database [closed]

How to do indexing and what is the use? Please explain in such way so I can use it practically.