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

learn more… | top users | synonyms

3
votes
2answers
263 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
0answers
13 views

pt-duplicate-key-checker - DBD::mysql::db selectall_arrayref failed: MySQL server has gone away

I run pt-duplicate-key-checker a few months ago successfully. Now I receive on the same database (Galera, RHEL7) those errors DBD::mysql::db do failed: MySQL server has gone away On CLI I can do use ...
0
votes
1answer
19 views

Find unused indexes

I'm using the following query to find unused indexes: SELECT PSUI.indexrelid::regclass AS IndexName ,PSUI.relid::regclass AS TableName FROM pg_stat_user_indexes AS PSUI JOIN pg_index AS PI ON ...
1
vote
1answer
812 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 ...
3
votes
2answers
49 views

Define a proper index for a table

I have a table with millions of rows and something like 20 columns. This table have an unique identifier as primary key, but the most likely query is based on three varchar columns (col1 and col2 of ...
-1
votes
1answer
27 views

No INDEX variation seems to speed up the query

I have this query that orders prices based on the customer's NO being a possible pricegroup too (this is nicely cached as an ID in the pricegroups table too tho). SELECT p.`variant_id`, ...
0
votes
0answers
59 views

Suggestions for indexes or how to partition a table in SQL Server 2008R2

Table structure and DBMS SQL Server 2008 R2 I have a single table structure (flat) that has summarized sales data (YTD,MAT etc) by unique combinations of "Customer", "Product" and "Territory". ...
1
vote
1answer
128 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 ...
1
vote
1answer
56 views

Complex key performance problem (MySQL, InnoDB)

A query is painstakingly slow in both production environment and (fortunately reproducable) on my local dev box. Production is Linux and my dev is currently Windows 10 with MySQL 5.7.11: SHOW ...
3
votes
2answers
76 views

Better understand table indexing best practices

I'm not completely sure when would I start indexing my tables, when there are some criterias that I should consider: The tables are not that big, I think (1000 - 10,000 rows). The searches are made ...
0
votes
1answer
110 views

How to order the where clause and join columns in a covering index?

Simple query example: Select t1.A, t1.B, t1.C, t1.CustomerID from table1 t1 join table2 t2 on t1.CustomerID = t2.Id where t1.A = 'x' and t1.B = 'xx' and t1.C = 'xxx' I created a non clustered with ...
2
votes
0answers
40 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
1
vote
1answer
50 views

Simple update takes forever

On SQL Server I want to update a 0/1 interger value for a specific set of records. For some reason this takes very long (let it run for 2 hours, still not finished). I can't figure out what is wrong ...
2
votes
1answer
49 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 ...
5
votes
1answer
90 views

How to not “duplicate” a table if it has a unique constraint over many columns? [duplicate]

I have a very large table (35GB) that is unique over a combination of four of its columns. The table isn't very wide and the four columns it is unique over are the columns that are larger (in ...
11
votes
3answers
230 views

Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries. For the query below, SET STATISTICS IO ON; DECLARE @OrderStartDate DATETIME2 = '27 feb 2016'; DECLARE @OrderEndDate DATETIME2 = '28 feb 2016'; SELECT ...
0
votes
0answers
11 views

Postgres number of heap fetches greater than total rows

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
4
votes
2answers
327 views

What is the fastest way to fetch the last row from a table?

I have a PostgresSQL table, Prices, with the columns: price (Decimal) product_id (Int) Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the ...
1
vote
2answers
77 views

Does it make sense to create index with all columns included

We have a table with columns ID OtherTableId DataA DataB DataC DataD Does it make sense to create index on OtherTableID INCLUDE (ID, DataA, DataB, DataC, DataD) when we need all columns, or ...
1
vote
2answers
227 views

Clear missing index DMVs

How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details? I don't have the option to restart the server or taking it ...
2
votes
0answers
44 views

where to add a column with low cardinality, decimal type into an index?

I got a question regarding the best way to put a bad column for an index in an index, so that I can satisfy my query below, and similar others that follow the same pattern. on the query below, all ...
1
vote
2answers
53 views

What is the ideal composite index for this query?

What is the ideal Composite Index in MySQL for the following query on an InnoDB Table1? SELECT ColA FROM Table1 WHERE ColB = 0 AND ColC = 0 AND ColD = 0 ColA is the left-most Primary Key. ColB ...
2
votes
1answer
51 views

How to start indexing a new data warehouse

I am building a data warehouse (SQL Server 2012, Kimball Dimensional modeling, 750GB) that will supply data to reports (SSRS), cubes (SSAS) and various datafeeds. I plan to use an index tuning tool ...
0
votes
0answers
25 views

Query slow on 5 Mil fact table

I have a fact table with currently 5 mil rec. The record count will go up as ETL runs on daily basis. The below reporting dashboard query is taking 1.50 sec to execute. There are many number of ...
0
votes
0answers
49 views

Best Strategy to remove the unused or duplicate indexes

I have searched the Internet a lot about this but I'm still not comfortable making a decision. One of our reporting databases is 350 GB (it acts as a subscriber for transaction replication where ...
1
vote
1answer
63 views

Postgresql 9.5 BRIN index dramatically slower than expected

I have a table with over 12 million rows of log data and have migrated to Postgres 9.5 to take advantage of the new BRIN index because I have disk space limitations. I assumed that my situation was ...
1
vote
1answer
78 views

MySQL difficulty with optimizing slow query with index but without improvement. Explains tells otherwise

I've a slow query (~11 seconds) that I'm trying to optimize. But the use of indexes seems to not make any difference even thou Explain indicates otherwise. Let me walk you through my problem. So this ...
3
votes
1answer
42 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 ...
3
votes
1answer
69 views

Full text search in XML documents

I would like to do full-text search in XML documents. However, I can't get matches for attribute values (I get a syntax error if I provide the full attribute value including the : and / characters in ...
0
votes
0answers
35 views

How to make query COUNT, GROUP BY fast?

I have query something like this: SELECT (data->>'course_class_id')::BIGINT cc_id , COUNT(*) enrolled , COUNT(CASE WHEN (data->>'grade') IN ('A','B','C','D','E') THEN 1 ELSE NULL ...
3
votes
1answer
81 views

Using ILIKE with unaccent and with only right end wildcard

I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with ...
3
votes
1answer
916 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
30 views

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

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 ...
6
votes
3answers
130 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
59 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
78 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
67 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
44 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
144 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
120 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 ...
4
votes
2answers
77 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
33 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
57 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 ...
10
votes
2answers
330 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 ...
15
votes
4answers
1k 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
857 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
59 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
41 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 ...
3
votes
1answer
45 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
30 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" ...