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

learn more… | top users | synonyms

0
votes
1answer
28 views

Splitting the table to get more clusterization options - will it help? [on hold]

Complete theory. Let's assume we have a table with a few millions rows. The table has clustered index over PK, and it is important for quick fetching of particular rows. However, the table also has a ...
5
votes
3answers
103 views

Do Postgres GIN indexes need to be rebuilt periodically?

Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.
0
votes
1answer
44 views

Index on integer and string fields

A table has about 300M records. Each has an unique ID that starts with 13 to 14 digits (with the first digit on the left being either 1 or 2), and is optionally followed by a string of up to 7 ASCII ...
2
votes
1answer
60 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 ...
0
votes
0answers
65 views

Index has no gain vs normal query

I have a table with a timestamp field. The table is loaded with rows which have ascending order of this field (time increases). I tried the following query, prior to establishing an index (there are ...
1
vote
0answers
42 views

Why does the first query scan and the second seek?

Please could somebody explain why the first query below scans on every JOIN to the REFERENCE_VALUES table but the second query seeks on the same JOINs. The first query does have other JOINs but I ...
4
votes
3answers
83 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 ...
5
votes
1answer
97 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
24 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 ...
4
votes
2answers
70 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 ...
2
votes
0answers
29 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, ...
1
vote
1answer
42 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 ...
9
votes
2answers
180 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 ...
3
votes
2answers
241 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
1answer
712 views

MySQL: Optimize query with a range and distinct count

I have a query on a large table with millions of rows that looks like this: SELECT COUNT( DISTINCT clicks.tx_id ) AS unique_clicks_count , clicks.offer_id FROM clicks WHERE ...
14
votes
4answers
797 views

Should the index on an identity column be nonclustered?

For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column? The reason is other indexes will be created for queries. A query which uses a ...
3
votes
2answers
716 views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
2
votes
1answer
46 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 ...
0
votes
1answer
30 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 ...
4
votes
1answer
41 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 ...
0
votes
0answers
23 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" ...
1
vote
2answers
246 views

Should these two indexes suggested by the SSMS missing index feature be combined?

I'm trying to improve the performance of a specific query that is automatically generated by Entity Framework. I have run the query through SSMS, and it has suggested creating two missing indexes. ...
11
votes
1answer
2k views

What factors go into an Indexed View's Clustered Index being selected?

Breifly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
13
votes
4answers
837 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 ...
3
votes
1answer
704 views

'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?

I'm running PostgreSQL 9.3 on Mac OS X Yosemite. I try to create an unaccent lowercase trigram index. To achieve it I did this: mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; ...
0
votes
1answer
44 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
73 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 ...
1
vote
1answer
214 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
4
votes
1answer
46 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 ...
5
votes
1answer
217 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 ...
8
votes
3answers
146 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 ...
2
votes
1answer
46 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 ...
4
votes
2answers
72 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
20 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
2answers
112 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 ...
1
vote
1answer
27 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 ...
3
votes
1answer
167 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
1
vote
1answer
58 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 ...
0
votes
1answer
41 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
66 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 ...
4
votes
2answers
67 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 ) ...
1
vote
0answers
29 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 ...
0
votes
1answer
70 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, ...
9
votes
2answers
7k views

Multicolumn index and performance

I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries. The scenario: PostgreSQL 8.4, table with about one ...
0
votes
1answer
38 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
118 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. ...
1
vote
1answer
85 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 ...
5
votes
2answers
136 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
1answer
26 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
24 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 ...