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

learn more… | top users | synonyms

1
vote
1answer
72 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
44 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
63 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
81 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
77 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
54 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 ...
5
votes
1answer
249 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
49 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
158 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
27 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
117 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
106 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
109 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
83 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
74 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
45 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
161 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
57 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
83 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
88 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
155 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
55 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
24 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
32 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
281 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
51 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
200 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
38 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
120 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
104 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
77 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
156 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
135 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
255 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 ...
4
votes
2answers
103 views

Wide clustered index vs multiple narrow nonclustered indexes?

Say I have a contrived Student table like so: CREATE TABLE Student ( Id IDENTITY INT, SchoolId INT NOT NULL, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL ) ...
0
votes
0answers
102 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[...
1
vote
0answers
397 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 ...
6
votes
3answers
359 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"."...
2
votes
1answer
59 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 ...
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 ...