Questions tagged [index-tuning]

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

Filter by
Sorted by
Tagged with
1
vote
2answers
93 views

Speed up query execution. Can we add an index or rewrite the query

I've been working on this query handed to me by a developer who wanted to reduce the query execution time even further. Currently, it takes almost around 40 secs and outputs 262K records. Based on the ...
1
vote
0answers
77 views

PostgreSQL determine the optimum number of partial indexes

I have a table with 5 columns of interest: geo_x integer -- quantized longitude geo_y integer -- quantized latitude context integer -- the type of record hash integer -- a hash ...
5
votes
3answers
545 views

SQL Server 2016 not using index used in 2012

2016 plan here 2012 plan here I have almost identical tables with very small difference in row count. One on 2012, and the other on 2016. Indexing is identical. These VMs are in exact same ...
1
vote
2answers
235 views

PostgreSQL: why does an extra WHERE clause make an index-only scan slow?

I have a query that is being made 18x slower by the presence of a single WHERE clause. The query plan is an index-only scan and the clause in question looks at one of the fields in the index. This ...
1
vote
0answers
55 views

Execution plan using index that no longer exist

I have some odd behavior with an execution plan using an index that no longer exists. let me give you the background. We had some high CPU going on so I had a look what was happening and found a ...
4
votes
2answers
172 views

Columnstore index on multi-tenant database

I have a sql azure database that is used only for analytics. It is constantly being updated with data pushed from the OLTP system. The OLTP inserts/updates/deletes are pushed via webhooks to a ...
0
votes
1answer
78 views

Is my index too expensive to maintain?

I just indexed a view that returns 126 millions rows. There's a process that uses this view to update rows using a trigger and it's by far the most expensive process in my database. I'm monitoring ...
0
votes
1answer
48 views

How do I optimize query where searching for 'is not null' on either of two linked tables

I have a query like this: SELECT * FROM my_first_table a -- > 1 million records INNER JOIN my_second_table b -- > 1 million records on a.id = b.a_id LEFT OUTER JOIN my_third_table c --...
3
votes
1answer
939 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 ...
2
votes
1answer
99 views

Is there anything I can do to increase performance in the future once table gets bigger?

I created a table importing an Excel sheet with 600,000 rows. Most of the queries I will run will be using C_CustomerID, they will be like the example below: select * from testtable where ...
3
votes
2answers
2k views

MySQL - Large Table best practices/indexes

I have a table which currently contains around 33 millions rows of data, and is constantly recording more rows second by second. The query to pull data out of this table is gradually getting slower ...
1
vote
1answer
48 views

Query very slow - how to optimize

I have a query which is taking up to 7 minutes to execute. My Question is, how am I able to optimize this query? The EXPLAIN statement shows two bottlenecks which are following: table ...
5
votes
2answers
1k views

Ola Hallengren Index maintenance on servers with Availability Groups

I'm configuring the Ola Hallengren scripts for Index maintenance on all servers in may Availability Group cluster which has several AG's and databases which are not in AG's. If I select the "...
2
votes
1answer
371 views

Not searching GIN index when querying ANY(column)

In a Postgres 9.4.11 database, I have a large table of books, which has a one-to-many relationship with authors. Theoretically to improve performance, I've cached the author names in the ebook record ...
7
votes
2answers
2k views

Indexes and Statistics on Temp tables

I've just upgraded our Data warehouse to SQL 2016. I'm seeing some really interesting graphs in the Query Store (I love this feature!). Below is the weirdest example i've seen. 22 plans for the same ...
5
votes
1answer
596 views

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
4
votes
1answer
517 views

Does rebuilding a clustered index on an IDENTITY column do anything useful?

We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and ...
3
votes
3answers
492 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), ...
0
votes
2answers
338 views

Reverse pattern matching: find the longest prefix

I have a database with the phone rates. Rather simple one. Code Price ---- ----- 1 0.005 18 0.002 1888 0.000 Now I have a phone number arriving to the database: 18127551221. ...
1
vote
2answers
209 views

MySQL doesn't use entire index when combining join and range

I'm trying to optimize a simple query that joins between two tables and applies a range condition. From the explain plan below, you can see that the index inv_quantity_on_hand is only used partially (...
11
votes
3answers
13k views

Indexing a PK GUID in SQL Server 2012

My developers have setup their application to use GUID's as PK for pretty much all of their tables and by default SQL Server has setup the clustered index on these PK's. The system is relatively ...
4
votes
3answers
542 views

Should I use Index or Unique Key for a non PK Uniqueidentifier?

Using Sql Server 2014, I have several tables where the PK field is an int, but I also have a UniqueIdentifier field that I need to query by. This field will always be unique. I am adding indexes for ...
1
vote
2answers
103 views

What strategy does MySQL optimizer use when choosing between two indexes?

In question: Optimize a query with two range conditions the OP wanted to know if there were a way to optimize his query. In an answer given the query were rewritten as a LEFT JOIN, and that was the ...
8
votes
3answers
2k views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
3
votes
1answer
502 views

Performance impact of including result column in indexed columns

I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of: texelid ...
0
votes
1answer
66 views

Proper way to index table columns which can be filtered in multiple ways

I have a comments table with the usual fields in it, i.e. author, title, body, created_at, etc. There's also a field called "user_id" which stores the id of the user the comment is directed at. On ...
4
votes
2answers
6k 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 ...
2
votes
1answer
123 views

Good databases for unanchored LIKE condition

Basically I need a database that is good with queries like LIKE %abc%. I already tried PostgreSQL with GIN indexes and it is very good, but maybe there is something even better? I also tried MongoDB ...
3
votes
3answers
2k views

Performance of using a non-clustered index as primary key

Background I am designing a database for a record management system. It's early stages as I am considering what to use as a primary key: INT or UNIQUEIDENTIFIER. The reason for considering ...
4
votes
2answers
6k views

Applying indexes on columns used in join condition

In the query below,how beneficial it is to use index on columns used in join conditions? select * from table_a inner join table_b on table_a.col1 = table_b.col1 and table_a....
2
votes
3answers
153 views

Index utilization issues in SQL Server queries

These days I have to optimize queries written by someone long back and I don't have business info behind them.So trying to optimize them a safe way,without hampering business logic. While tuning ...
3
votes
3answers
838 views

Deploying Ola Hallengrens scripts - Index Optimization

Never before I used Ola Hallengren Scripts . I am planning to deploy the Ola Hallengren scripts for Index Optimization for the first time. I need to deploy them in PROD so I want to be more careful. ...
0
votes
3answers
56 views

Changing Auto-Growth Setting to MBs vs Log Drive Huge Occupancy

These are our PROD Databases current settings and total information about log file utilization. We are using SQL Server AOAGs 2014 Auto-Growth I am planning to set 512 MB Recently we have seen huge ...
1
vote
2answers
305 views

Shrinking T-Log file every weekend basis

Recently our SQL Server Log drive which has all T-Log files starting growing rapidly. Considerations We expanded drive space to 100 GB. It is AOAG environment, SQL Server 2014. We have regular ...
3
votes
1answer
99 views

Deciding on optimal index combinations

I have a table with 8 columns, no primrary key, and many, many indexes. Now I wonder whether they're as optimal as can be. The table looks as follows: CREATE TABLE `search` ( `a` bigint(20) ...
4
votes
2answers
1k views

Performance refactoring: Trying to avoid the table scan

I have a proc containing a query joining a few tables and I am having some performance issues. The main table (which is a huge table) has a PK and a few NC indexes. CREATE TABLE [dbo].[TableA] ( ...
2
votes
2answers
422 views

Why don't I get an index seek?

Working on SQL Server 2014 and trying to optimize a query. One portion of it is doing a table scan. I distilled the issue down to the simplest form. There is a field in a table (3rd party product's ...
7
votes
3answers
2k views

Storing multiple tags on analytics database

I would like to store user purchase custom tags on each transaction, example if user bought shoes then tags are "SPORTS", "NIKE", SHOES, COLOUR_BLACK, SIZE_12,.. These tags are that seller ...
0
votes
1answer
49 views

adding an index on different fields

I have a query like this: SELECT "btr_orders".* FROM "btr_orders" WHERE "btr_orders"."user_id" = 40000000 AND ( premium_until > now() OR intermediate_premium_until > now() ) AND (...
0
votes
1answer
446 views

Why my function-based index is not used?

I have a table EVENT with a column DATE_RECEIVED (the type of this column is DATE): CREATE TABLE "EVENT" ("EVENT_ID" VARCHAR2(60 BYTE) NOT NULL ENABLE, [...] "DATE_RECEIVED" DATE, [...] ); I ...
0
votes
1answer
254 views

Why does it use temporary? (MySQL)

MySQL 5. I have the following table (filled with many rows): CREATE TABLE `SectorGraphs2` ( `Kind` tinyint(3) UNSIGNED NOT NULL COMMENT '1 - продюсер, 2 - жанр, 3 - регион', `Criterion` tinyint(...
0
votes
2answers
90 views

Does indexed column with millions of different values make sense?

I noticed that one of my queries was pretty slow, taking about two seconds to execute. This is the slow query: SELECT EVENT_DATETIME, EVENT_EVENTTYPE FROM EVENT WHERE (EVENT_ID = :1 AND ...
5
votes
4answers
2k views

postgres: index to SELECT strings like '%foo%';

I have an expensive, periodic PostgreSQL 9.3 query of the form: SELECT * from mytable where name NOT LIKE '%foo%'; foo is actually a constant here that never changes. The query is expensive because ...
4
votes
1answer
408 views

Using sys.dm_db_index_usage_stats for unhelpful or unused indexes

I'm trying to discover indexes which may be in place which are only adding cost with no value to our database. I've come across sys.dm_db_index_usage_stats and from MSSQL Tips the following: sys....
16
votes
3answers
4k 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....
4
votes
3answers
283 views

Is there any reason for an index to contain the primary key?

I inherited a project using SQL Server 2005. Recently there were some performance problems and I started looking at the indexes, and found _dta_index_survey_25_135059617__K1_K10_K19: id ASC, ...
2
votes
1answer
447 views

Optimize query with join and null predicate [SQL Server]

I have the following query which I am having problems optimizing: SELECT TOP (100) this_.Id AS Id80_1_, this_.UserAgent AS UserAgent80_1_, this_.IP AS IP80_1_, this_.Guid AS Guid80_1_, ...
2
votes
1answer
156 views

When is a multi-attribute index less beneficial?

I'm studying non-key organizations to know when are they better to use than classical key ones. With the following SQL queries, which of them takes less benefices from the multi-attribute index on R ...
1
vote
1answer
1k views

Postgres index size calculation

Is there any way to determine a rough estimate of the size (on disk) of an index before creating it? The size of the table and each column are known. I am particularly interested in GIN indexes. Also ...
2
votes
2answers
79 views

Avoiding Key Lookup when using MIN and MAX

I have a somewhat simple table that takes Id guid (pk) SoftwareId guid (FK to software table) OnlineUsers int OnlineDevices int SampleDate datetime SDHour (persisted computed field, holding ...