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

learn more… | top users | synonyms

5
votes
1answer
151 views

Datawarehouse indexing

I have a table with nearly 6m records. The business key that identifies a unique row is quite large. Our update processing is now taking a lot longer since I added this new table for a new cube. I ...
3
votes
1answer
45 views

Postgres reduce by key if key is not null

I need to select N rows with seq > X which reduce_key is null and for each row group with the same reduce_key select the one with the biggest seq. Result should be strictly sequential (but with ...
2
votes
0answers
35 views

MySQL Performance issue: ~360ms to fetch 120 rows with 10 columns

I have the following tables: CREATE TABLE `events` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'draft', `publish_at` datetime DEFAULT NULL, ...
4
votes
3answers
243 views

Make queries and BCP faster on a very large table

This question is somewhat of a continuation to this: Huge data and performance in SQL Server, but different enough to merit its own question. I'm going to copy and paste some of the text from that ...
2
votes
1answer
106 views

PostgreSQL not using index during count(*)

I have a COUNT(*) query in PostgreSQL that runs often, and looks like: SELECT COUNT(*) FROM customer WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); This query ...
10
votes
2answers
618 views

Good reasons to use SELECT… WITH XLOCK?

I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables ...
1
vote
1answer
99 views

MySQL query taking too long to execute

My MySQL query is taking around 20 minutes to execute which I feel is excessive. SELECT hyp_id,MAX( player_count ) AS MAXPLAYERCOUNT , MAX( viewer_count ) AS MAXVIEWERCOUNT , SUM( player_count ...
0
votes
1answer
45 views

Will this SQL snippet REINDEX my GIN index? Why is it renaming an old index onto another?

I have a large PostgreSQL 9.3 database. One index on one table is quite large and appears to be bloated. I am using pgcompact from pgtoolkit to reduce database bloat without heavy table locks. However ...
2
votes
1answer
66 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE UNACCENT(LOWER(a.name)...
14
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 dbo....
0
votes
1answer
92 views

Postgres datetime sort slow and not using index

Using Postgres 9.4, I'm trying to order a query by an indexed datetime column. explain SELECT g.*, array_agg(gp.platform_id) as platform_list, array_agg(gm.metacritic_id) as ...
0
votes
1answer
78 views

NonClustered Index Scan Problem

I had an issue with a Clustered Index Scan. So I got all columns in the Output list of the Index Scan in SSMS and I created a NonClustered Index for these columns. Now however, I do not see any ...
4
votes
1answer
56 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
48 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 ...
5
votes
1answer
254 views

Reasons to use covering indexes

I was asked "when do we need to bring covering indexes to play, and why?" by a DBA. My explanation was: It allows engine to pull all of the relevant data directly from the values in the indices ...
2
votes
1answer
50 views

SQL Server 2008: Unused Clustered Index

I have an MS SQL Server 2008 R2 database that contains a table that has just under 100 million rows & is both read and write heavy. It contains Tweets, and the id of each Tweet is one of the ...
8
votes
3answers
159 views

Tuning query with multiple joins

i have this query .. 214 Execution/min , 44.42 Avg CPU(ms) is there a way to make it much faster SELECT P.Id id0, P.ProgramId ProgramId1, P.ProgramName ...
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
29 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 = ...
0
votes
0answers
32 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" ...
4
votes
2answers
118 views

Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index

On my query 1st time it was taking almost 39 seconds and showing me non-clustered index is missing and Scan count 1, logical reads 14553, physical reads 0, read-ahead reads 0, lob logical reads 0,...
1
vote
1answer
28 views

Theory of indexes for query with joins

I found the following talk by Zardosht Kasheff enlightening: http://www.infoq.com/presentations/Index-SQL but it only covers the case of simple queries on a single table. Is there something similar ...
1
vote
1answer
115 views

Using composite indexes with date ranges

I have a table of rules that are activated depending on the date. id - name - value - start_date - end_date 1 - ABC - 10 - 2015-12-01 - 2015-12-31 2 - DEF - 15 - 2016-02-01 - 2016-02-29 ...
1
vote
1answer
133 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 ...
3
votes
1answer
87 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 ...
0
votes
1answer
75 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, avl_vehiculo....
0
votes
1answer
47 views

Tuning read-intensive queries - Oracle

Now this might be very vague but Im sure I can provide more info on them.. my query is that is it recommended to use tablespaces if different block size because I have a table in which number of reads ...
0
votes
1answer
170 views

Why is the optimizer doing a table scan vs Index Seek

Probably a straight forward answer, but I'm too bleary eyed to see it. Have a simple query that is pulling the top 100 rows based on a char column. The column has an index on the where column. ...
4
votes
2answers
79 views

Does a covering nonclustered index help to answer Select * queries?

Our application developers run a lot of SELECT * queries against our database system, unfortunately. The missing index suggestions of sys.dm_db_missing_index contain a lot of cases that indicate to ...
1
vote
0answers
35 views

Reasons why plans of sys.dm_exec_cached_plans do not match actual plans?

I re-assess index / query optimization and actually focus on key/bookmark lookpus that could be eliminated. Using a script of Jonathan Kehayas I go through the list of query plans that used those ...
2
votes
1answer
68 views

How to make unique check without creating heavy index in postgres

I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and ...
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....
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` ...
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
1answer
167 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
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 ...
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
372 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'...
1
vote
1answer
109 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
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. ...
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 ...
5
votes
2answers
220 views

Howto Identify Clustered Index Candidates

I find myself in a situation where I have inherited the db and people complain about database performance. I identified about a dozend of tables not having a clustered index or a not carefully planned ...
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
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), ...
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), ...
0
votes
1answer
81 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 ...
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 ...
4
votes
2answers
142 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 ---...
10
votes
1answer
261 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 ...