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

learn more… | top users | synonyms

1
vote
0answers
25 views

Index comparison

On my Production server (SQL2008) I have a query that returns suggestions of indexes. SELECT * FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats gs on gs....
2
votes
1answer
89 views

Why does SQL Server use this index?

I have two indexes on a table with 786602 rows: CREATE NONCLUSTERED INDEX [IX1] ON [dbo].[O] ([A] ASC, [B] ASC) INCLUDE ([c1], [c2], [c3]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ...
1
vote
2answers
85 views

How to determine the best index on a table with 3 nullable columns?

I am examining a users table similar to this: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `password` ...
0
votes
1answer
56 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
52 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
25 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
34 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 ...
4
votes
2answers
378 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: db.getCollection('product'...
2
votes
1answer
52 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
110 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) ...
2
votes
3answers
124 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), ...
7
votes
0answers
129 views

How to do Statistics in Partial Indexes of PostgreSQL efficiently? [duplicate]

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
39 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: ...
2
votes
3answers
105 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
172 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
82 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
143 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
117 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': df_youtube[...
10
votes
1answer
263 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 ...
6
votes
3answers
435 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 "products"."...
7
votes
1answer
111 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
459 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
715 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
1k 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
32 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
61 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
64 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 ...
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
181 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 condition....
1
vote
0answers
126 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
199 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
64 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
63 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
428 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
457 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
488 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
59 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
198 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
326 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
156 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_, rtr....
1
vote
2answers
75 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
0answers
392 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
326 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
233 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 `s`.`...
5
votes
2answers
246 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 `...
6
votes
1answer
365 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
267 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
397 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 it'...
1
vote
1answer
89 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 ...