The process of determining which indexes are useful and which are not.

learn more… | top users | synonyms

0
votes
1answer
52 views

Why is this PostgreSQL query table scanning

I am running PostgreSQL 9.3. The database is of all of the medical NPIs (national provider numbers). I have created the following indexes: CREATE INDEX ...
0
votes
1answer
49 views

MySQL Finding index size for compound unique indexes

I'm using following query to get index and data sizes for tables in database. But unfortunately it doesn't show index size for compound unique indexes. How can i get index sizes for compound unique ...
2
votes
0answers
22 views

Combine several missing index suggestions with one index?

I investigate on missing indexes recommendations of my sql server engine. Looking at the most useful ones (for a specific table) that are candidates based on their potential usage and potential ...
0
votes
0answers
29 views

Minimum Oracle table size making IOT advisable?

I have inherited an IOT that doesn't seem to merit it because: only 25k rows fairly pure join table, non-index fields rarely queried total index width only ~125 bytes total row width only ~160 bytes ...
3
votes
2answers
122 views

MongoDB uses only one index on simple query with sorting

So the problem is that I'm having a huge collection and I'm trying to run a query that filters and sorts on only fields that have indexes on them. I have these two indexes: ...
2
votes
1answer
47 views

Missing indexes dmv count varies every day

I have a question to the missig index dmv. BOL says: Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. ...
1
vote
1answer
106 views

Create index clustered X NonClustered with include

I have a table like : create table x (a varchar(20), b varchar(30), c varchar(30), primary key clustered(a asc)) so I'm wondering if I create an index like: Create nonclustered index test on x(a) ...
8
votes
2answers
207 views

is there a work around for when you want to put an OR inside a filtered index?

is there a work around for when you want to put an OR inside a filtered index? create index FIDX_tblbOrders_sdtmOrdCreated_INCL on dbo.tblBOrder(sdtmOrdCreated) INCLUDE (sintMarketID, ...
2
votes
3answers
108 views

Performance of rare SELECT vs. frequent INSERT in timeseries data

I have a simple timeseries table movement_history ( data_id serial, item_id character varying (8), event_time timestamp without timezone, location_id character varying (7), ...
6
votes
0answers
125 views

How to do Statistics in Partial Indexes of PostgreSQL efficiently?

This answer motivated think again my following tables but I am still uncertain how to store variables/results from data analysis for quick selection in post-processing. If stored, I think it is well ...
1
vote
0answers
27 views

DMV Index proposals still reported as missing

We have recently converted a big client running our patient/journal application from ORACLE to MSSQL, and after the conversion we have had quite a few performance issues. After running the DMV query: ...
1
vote
3answers
99 views

What indexes should one use for this two-table setup?

I have the following two-table setup: CREATE TABLE relationships ( id bigint(20) NOT NULL AUTO_INCREMENT, type varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (id), ...
3
votes
1answer
104 views

Function Based Index with XPATH extractValue

// Oracle Version 11.2.0.4 Standard Edition // OS RedHat I am trying to create a function index on a large table, based on using an XML object. So I have done stuff such as: CREATE INDEX ...
0
votes
1answer
54 views

MySQL Query causing high CPU and taking forever to execute

this below query takes a long time to execute in excess of 120 seconds, upon checking the explain plan it seems to be using using where, using temporary; using filesort. Since the application is a ...
4
votes
2answers
106 views

Extremely slow query on indexed column

I'm getting a extremely slow query on an indexed column. Given the query SELECT * FROM orders WHERE shop_id = 3828 ORDER BY updated_at desc LIMIT 1 explain analyze returned: QUERY PLAN ...
0
votes
0answers
66 views

MongoDB Slow updates

My application is doing extremely slow updates, despite my query being covered by indexes. My query is: query = {'spot': df_youtube['spot'].values[key], 'segment': ...
10
votes
1answer
210 views

How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use ...
5
votes
3answers
174 views

Slow query performance due to temporary file?

Here is the query: SELECT "products".* FROM "products" WHERE (status > 100) AND "products"."above_revenue_average" = 't' AND ("products"."category_id" NOT IN (5)) ORDER BY ...
7
votes
1answer
107 views

Clustered Index Help, where did i go wrong?

I was asked to review a very busy table and identify any areas of improvement. I have very limited power to change the table just the indexes. Table information 240 columns ~5 million rows Read ...
1
vote
0answers
139 views

Script to find unused indexes over a period of time doesn't work in Oracle 11g

The statspack_unused_indexes.sql script described in this page would be incredibly useful to me, but it has been coded for an earlier version of Oracle. When I try to run it on 11g, I get the ...
2
votes
2answers
378 views

where is the index physically located in MySQL database

Just wondering where is the index info physically located in MySQL database if I have indexed a table with ALTER TABLE contacts ADD KEY (columnName); ? it will stay with that table and add a new ...
0
votes
4answers
903 views

Why Clustered Index property is enabled on few columns and not on others?

I heard that there can be only one Clustered Index? I may be wrong, but I am sure I read somewhere. In one of my SQL Server 2005 table, I have following columns: SerialNum ------- BigInt -------- ...
1
vote
1answer
30 views

Index design question

This query runs for 60 seconds: SELECT mychecklis0, .... FROM dbo.MyChecklistStatus mychecklis0_ WHERE mychecklis0_.AppFormSubSectionId=@P0 AND mychecklis0_.ProfileId=@P1 There is an index created ...
2
votes
1answer
58 views

Index is seeked and scanned - is there a risk for seek to fail after modification?

I have an index that has been seeked 230.000 times and scanned 130.000 times. In order to improve the queries that have scanned, because they use an additional column I guess, I would like to change ...
2
votes
1answer
52 views

Index usage and update statistics do not correspond?

In order to discover unused and wrong indexes in sql server I went through the following article: https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/ It gives two queries ...
6
votes
1answer
131 views

Ola Hallengren Index Script not Reindexing

First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on. I have Ola's script set as @Databases nvarchar(max) ...
9
votes
4answers
2k views

How will large index INCLUDE fields effect system performance?

This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index. I’m trying to improve performance in an slow and unstable database application. In some ...
7
votes
3answers
142 views

Can PostgreSQL use nulls in its indexes?

I've been reading this book which says that The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this ...
1
vote
0answers
121 views

Slow Tally Tables

I have a pain point with a relatively large application concerning 'tally tables', which are used for keeping track of the number of items against 'campaigns' (placed by a client), and 'publishers' / ...
1
vote
3answers
102 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
57 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
2
votes
1answer
60 views

Ways to Reduce Database Size and possibly improve performace. Untouched enviroment.

I am new to an environment. The Databases are virtually untouched by a seasoned DBA. I am looking for ways to reduce DB size and possibly improve performance. Unfortunately, I only have WEb edition ...
2
votes
1answer
162 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 ...
4
votes
2answers
398 views

What is Transaction Path Analysis?

I came across the term Transaction Path Analysis which I am not familiar with. A Google search resulted in only a few results, most of which seem to assume everyone knows what it means. From these my ...
2
votes
1answer
296 views

Clustered index on a column with low selectivity

I have a table with a lot of columns. Two lookup functions are performed: 1) lookup by using column name (this happens rarely, maybe twice a season), and 2) lookup by column date. The column name has ...
0
votes
2answers
21 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
1answer
58 views

Index based on another index [closed]

Assume a mysql table with columns A, B, C, ... . There are one composite index based on columns A+B+C. Combination of values A, B and C is unique, so this index forms the primary key. Now, a second ...
3
votes
1answer
185 views

Excessive CPU use in vendor SQL Server DB

Background: I've got a SQL Server 2012 DB provided by a vendor so modification of queries and tables is limited. We do own the DB though, so we can add and maintain indexes. Indexes haven't been ...
1
vote
2answers
229 views

mysql multiple column index Cardinality order

How does Multiple-Column Indexes work in MySQL? How is it stored internally? consider a table with Multiple-Column Indexes on two columns (X,Y). If X has High-cardinality than Y i.e., X is less ...
1
vote
1answer
111 views

mysql query choosing an incorrect execution plan

I have a primary with an dependant subquery as a where condition that is choosing a wrong execution plan, and therefore is running for ever. Main query: SELECT rtr.id AS id156_, ...
1
vote
2answers
62 views

Reorganize Index Task [closed]

Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance? Is this an adequate step of triage for degraded application/query performance?
1
vote
1answer
139 views

Dropping old and creating a new clustered index

I am wondering what is the best approach for dropping and creating a new clustered index on a table within SQL Server 2008. My primary concern is the length of time it will take to perform this ...
0
votes
1answer
311 views

SQL Server 2008 - Question about index behaviour

I have a general question about advanced issues regarding index behaviour. In short, roughly one year ago, we dynamically dropped and recreated a number of indexes with different filters, but using ...
1
vote
3answers
181 views

Index optimization and cardinality

I'm by no means a DBA expert. Rather, a Magento developer having to troubleshoot some slow queries. I'll jump right in -- here's my attempt to EXPLAIN the troublesome query: EXPLAIN SELECT ...
5
votes
2answers
240 views

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS ...
5
votes
1answer
284 views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
0
votes
0answers
179 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 ...
4
votes
1answer
326 views

Sudden poor SELECT performance on a large table with existing indexes

My company uses a software suite in which the only thing I can truly modify is the database. We've always had performance issues (mostly hardware problems that we hope to resolve soon), but lately ...
1
vote
1answer
85 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 ...
1
vote
1answer
49 views

Optimize view (and underlying table) for averaging timestamps into hours

I have this table: CREATE TABLE spp.rtprices ( "interval" timestamp without time zone NOT NULL, rtlmp numeric(12,6), rtmcc numeric(12,6), rtmcl numeric(12,6), node_id integer NOT NULL, ...