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)

3
votes
0answers
106 views

What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?

According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately ...
6
votes
2answers
734 views

Effect of an index on update statements where update column is not in an index

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute. While tuning my database to improve performance, I keep ...
6
votes
1answer
369 views

What happens when we add an index to an existing table with a large amount of data?

I've a table which will contain around ~15 Million records. Now I need to add an index to the table. Adding an index will take some time to update every entry in the table. I'm quite confused ...
1
vote
1answer
64 views

Store Mysql Index in separate .Ibd file?

Is there any option to store table index in separate .ibd file? something like file_per_table mode? Becuase my table is growing more than 200gb. Table .ibd file have index for that table also. so If ...
-1
votes
1answer
57 views

Verify this TSQL to find the indexes fragmented over 50%

select OBJECT_SCHEMA_NAME(IPS.object_id) 'schema', OBJECT_NAME(IPS.object_id) as table_name,index_type_desc, round(avg_fragmentation_in_percent,2) 'avg_fragmentation_in_percent', si.name ...
4
votes
1answer
125 views

Index not being used in SELECT query

I have a table of about 3.25M rows with the follow format in Postgres 9.4.1 CREATE TABLE stats ( id serial NOT NULL, type character varying(255) NOT NULL, "references" jsonb NOT NULL, ...
3
votes
1answer
164 views

Do WHERE IN queries reindex the table after each set within the query?

I have a basic WHERE IN query that takes too long (30+ secs) to process. DELETE FROM `table1` WHERE ( djID, localID ) IN (("128","26"),("110","32")) The table I'm querying on has about 20 million ...
4
votes
2answers
105 views

Optimize a query with small LIMIT, predicate on one column and order by another

I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times: Query #1 EXPLAIN ANALYZE SELECT posts.* FROM posts WHERE posts.source_id IN ...
13
votes
3answers
1k views

Why would SQL Server ignore an index?

I have a table, CustPassMaster with 16 columns in it, one of which is CustNum varchar(8), and I created an index IX_dbo_CustPassMaster_CustNum. When I run my SELECT statement: SELECT * FROM ...
8
votes
2answers
224 views

Are columns which are not indexes, sorted on disk together with index?

Are columns which are not indexes, sorted on disk together with index, in MySQL, in MyISAM and InnoDB? An incorrect thought that I started to write: I think that probably not, since they are not ...
5
votes
2answers
132 views

Rebuild index in SQL Server does not reduce fragmentation

I am trying to reduce the fragmentation of my indexes. First of all I rebuild all indexes with fragmentation higher than 30%, but the most of my indexes still with high fragmentation or does not ...
4
votes
1answer
52 views

Index not being used for logical operation

We have a MySQL table with a few million rows. We have set of users identified by a flag which int value. We are facing a problem that mysql is not using index. For example, following query is very ...
3
votes
1answer
47 views

Fastest way to find (rare) rows with a null value among millions of rows?

If I have an addresses table with ~ 4 million rows in a Postgres database. Is there a way to optimize the following query so that I can find the few rows where address_id is NULL? Sometimes I need to ...
1
vote
1answer
104 views

SELECT Causes Performance Issue [closed]

I have a SQL SELECT statement that has joins on 27 of our warehouse tables. Original statement was taking more than an hour everyday so I decided to optimize it. I created some indexes but still it ...
3
votes
3answers
132 views

Manually create index fragmentation

Does anybody have a good approach to manually create a specific fragmentation to an index, ideally without increasing the number of rows in the underlying table? I need this for the purpose of a ...
3
votes
1answer
54 views

MySQL: Add column to unique index or add new index?

For a MySQL database, I have an InnoDB table name user_metrics with the following columns: id INT(11) NOT NULL AUTO_INCREMENT, userid INT(11) NOT NULL, name VARCHAR(32) NOT NULL, value ...
7
votes
1answer
153 views

Improving a DbGeography query

I am still new to database administration and I'm trying to optimise a search query. I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU ...
4
votes
2answers
74 views

Indexed but still slow during query

I have a table that stores REST requests and responses: CREATE TABLE ws_call_logs ( id BIGINT NOT NULL, ws_method_id BIGINT NOT NULL, device_id ...
1
vote
0answers
24 views

How to bucketize using Query optimal bucketization algorithm

I am trying to implement query optimal bucketization algorithm (QOB). The input of the algorithm is D=(V,F) where V is the value of the attribute and F is the frequency of occurrence. For EXAMPLE D ...
4
votes
1answer
229 views

Do indexes get transferred with pg_restore

I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore). However, it looks like it did in my recent dump/restore and I am ...
0
votes
0answers
31 views

Covered vs Noncovered Index & CRUD Sprocs question

I have what is a very simple question for most here I am sure. I would like to point out while i can run a query with the statistics on and get the answer, I would very much like an "under the hood" ...
3
votes
1answer
54 views

Index not being used in PostgreSQL

I have three tables, call them table1, table2 and table3. table1 has a foreign key column referencing table2, and table2 has a column referencing table3. Both foreign key columns have a B-tree index ...
3
votes
1answer
44 views

B Tree and B+Tree differences

I'm studying B+ Tree and B Tree and I would like to understand two things about it, if someone can clarify it to me I would appreciate: Why can I store more search keys on an B+ Tree Index? My guess ...
7
votes
1answer
143 views

Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it ...
-1
votes
1answer
66 views

How to fix slow MySQL Joomla query(s)

I have a few slow mysql queries and i read that fixing it is easy by just adding indexes to the table. But as of what i am supposed to be indexing in these queries i do not know. (Or how) First query ...
1
vote
2answers
108 views

Are these database indexes correct for my search

I am new to Database Administration. I am following information provided by: http://www.brentozar.com/blitzindex/ I have a table VehicleMonitoringLog. When I execute the sp_BlitzIndex script it shows ...
0
votes
1answer
37 views

Why is MySQL 5.5 ignoring this index?

I have a pretty simple query that is not using an index I expect it to because of the WHERE. Table: CREATE TABLE `cba_device_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `device_id` varchar(30) ...
0
votes
1answer
55 views

Indexing a date field on MongoDB - best practice

Hi We would like to index a date field on a potentially large collection. Is indexing a date-time field a good practice? Or should we stick to a date field (e.g. 07/12/2017) indexing (both of the ...
1
vote
1answer
45 views

What options are there for rebuilds of indexes on large tables for a data warehouse?

I have a bulk loading process that loads millions of records into a few fact tables within a warehouse. Those tables are primarily clustered on time. The non-clustered indexes are in place for how the ...
3
votes
1answer
68 views

calculating the total number of rows in a table - fails when it is a heap - is there any fix?

I have a procedure that I use to find out how big is a table. select * from sys.objects where name = 'WMS_SLALoad' -- to get the object_id of my table -- in this case it is 2041058307 SELECT ...
2
votes
1answer
19 views

Why does PSQL not allow hash indexes with Bitmapscan?

For doing a simple equality query such as EXPLAIN SELECT * FROM table where x = 1; I see that a Bitmap Index scan is done using the BTree indexes. Apparently, Bitmap Index scans disable Hash ...
0
votes
1answer
18 views

How does the correlation statistic parameter affect the cost of index scan?

I have the table customers having customerid SERIAL as a primary key. Initially I had the following statistic for that column: attname n_distinct correlation customerid -1 ...
2
votes
2answers
66 views

Performance issue during count Postresql 9.4 - Creating index on pivot table key…?

Disclaimer: I am new to Postgres, I need some advice... I have a huge table, with full of poker hands.(will contain 20M records, right now 2M records in it) aggregated_hands I have a pivot table, ...
1
vote
1answer
92 views

Postgresql: Optimize finding all distinct JSON keys from a colulmn

I'm looking for a way to index / optimize finding only the keys in a JSONB column. The use case is an ETL operation where I need to take data stored in the JSONB column and output it as delimited ...
0
votes
1answer
109 views

How to optimize MySQL SUM query even after adding index

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table ...
2
votes
2answers
53 views

Index-refresh in SQL Server

Is there any way to identify when a specific index was last rebuilt; unless it is used as a batch maintenance activity? In SQL Server 2008R2 or in advanced versions.
3
votes
1answer
78 views

mysql not using index on production query, yet does on staging and local

I have this query: select m.id meal_id, SUM(FORMAT( ((((fi.calories / fi.weight_in_grams) * w.GRAMS) * i.amount) / r.serves) * mr.servings , 3)) calories from meals m join ...
2
votes
2answers
487 views

Why SQL Server has 900 byte index size limit

Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer. I can think of this way to answer this question. Page size ...
1
vote
1answer
80 views

Discover missing foreign keys and/or indexes

We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes. I'd ...
11
votes
1answer
183 views

Dropping unused indexes - Assessing the unexpected dangers

We have a very large database with hundreds of unused indexes according to DMV statistics, which have been accumulating since the server was last rebooted in July. One of our DBAs made the following ...
1
vote
0answers
56 views

Which indexes should I create?

I need assistance in which indexes to create on a table, and the best approach for querying it. We use PostgreSQL. Here’s the table: create table myt ( the_time timestamp, oid int, ...
0
votes
1answer
47 views

Is there a way to optimize this using indexes?

I'm just now learning indexes, and I was wondering if it's possible to optimize this code using them. Currently this code runs in 1.4 seconds. SELECT C.yearID as year, name as teamName, ...
1
vote
0answers
31 views

Oracle unable to extend index but I have many space free

I have application using Oracle database, and when I'm trying to call my pl/sql function which inserts many rows in tables I get an error: unable to extend index MY_INDEX by 128 in tablespace ...
0
votes
0answers
41 views

adding new column slows down select statement

I have a table with a single column that is the primary key Here is the create table statement: CREATE TABLE `test` ( `phone_number` varchar(18) collate utf8_unicode_ci NOT NULL, KEY ...
0
votes
1answer
73 views

Improve performance of query with many joins

From the following explain plan: explain SELECT imei, cliente_avl_equipo.id_cliente AS idcliente, cliente_avl_vehiculo.grupo AS grupo, avl_vehiculo.id, ...
0
votes
1answer
85 views

What is the cause of high index fragmentation in SQL server?

We have an application which is using a SQL Server database. As the application is in use for many years, its database is of huge size, resulting in slowness of application. It was noticed that the ...
2
votes
1answer
76 views

MySQL performance problem

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server: +-------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
2answers
183 views

slow query on mysql innodb table with 2 million rows

I have a table with about 2 million rows (growing at about 1 million every 3 months) and am having trouble with slow queries. I've tried changing my query and indexes (ensuring left most order) to no ...
1
vote
0answers
62 views

SQL Server index rebuild cause database growth

I have a 80GB database with about 20GB of indices size. Whenever I build the indices online the database file will grow to gain 20GB of unallocated space, and if the unallocated space is less than ...
6
votes
1answer
172 views

Why is my SP_HELPINDEX showing a lot of indexes?

Using sp_helpindex My_table I have this list: But my table has only these indexes: Why is this happening? I'm using a query to search for "equal indexes" in a particular table. This query is ...