Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [index-tuning]

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

97 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
12
votes
0answers
689 views

Splitting Snapshot files with MaxBCPThreads for Transactional Replication

I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm ...
4
votes
1answer
269 views

Guidelines on best indexing strategy for varying searches on 20+ columns

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The problem: ...
3
votes
0answers
30 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
3
votes
1answer
29 views

Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

I have a big table (30 millions rows) defined like this: CREATE TABLE `drawing` ( `id` int unsigned NOT NULL, `artist` int unsigned NOT NULL, `imageUrl` text NOT NULL, `uploadDate` timestamp NOT ...
3
votes
0answers
137 views

Best Way to Index Multiple Effective Date Columns

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
3
votes
1answer
624 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
2
votes
1answer
104 views

How to optimize the execution of query that takes too long

I am working with a query that is taking around 53 minutes to execute, which I feel is excessive. Maria DB version: Server version: 10.1.38-MariaDB-1~stretch mariadb.org The declaration of the ...
2
votes
1answer
59 views

Does a zero value in innodb_index_stats.rows_per_key mean the index is over-specified?

I'm taking care of an elderly 5.5.41 Mysql database and I was taking a look at the indexes with the following query: select i.table_name, i.index_name, i.rows_per_key, coalesce(t.rows_read, 0) as ...
2
votes
1answer
378 views

Oracle using sub-optimal index on bind variables

Let's say you have a table, called ORDERS, which contains ORDER_NUMBER, CUSTOMER_ID, and ORDER_DATE (among other fields). And you have one index on ORDER_NUMBER, another on CUSTOMER_ID, and third ...
2
votes
1answer
277 views

PostgreSQL is not using index with large data

Enviroment My PostgreSQL (9.2) schema looks like this: CREATE TABLE first ( id_first bigint NOT NULL, first_date timestamp without time zone NOT NULL, CONSTRAINT first_pkey PRIMARY KEY (...
2
votes
0answers
157 views

SQL Query produced by Umbraco app & NHibernate ORM - help!

https://www.brentozar.com/pastetheplan/?id=SJ1BhSun This is a sample of a query that this CMS called "Umbraco 5" runs against its backend DB, which is currently hosted on SQL 2008R2. This query runs ...
2
votes
1answer
63 views

best way for creating Index in mysql (multi-column/table)?

i have a big problem with my database.. I need a very fast query over 2 tables. 1. table: (node) ~42.500.000 entrys 2. table: (waynode ~51.200.000 entrys The following query is much to slow: ...
2
votes
0answers
137 views

Add index to mysql table with ndbcluster engine

I'm having trouble while adding index on mysql table with ndbcluster storage engine below is the error . [mysqlcluster ver. 7.4 GA] mysql> alter table `sks_staff_office` add index `pid_index` (pid)...
2
votes
0answers
71 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 ...
2
votes
0answers
143 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 ...
2
votes
0answers
85 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 ...
2
votes
0answers
95 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
0answers
899 views

Is there some tricks to better index a URL column in postgres?

I'm working with a table that needs fast retrieve rows by its url column. But they all will be starting with http://www.: http://www.google.com http://www.amazon.com http://www.stackoverflow.com I ...
2
votes
0answers
395 views

Unique Index much bigger than the table itself

Pg 9.3 on debian 7. I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
2
votes
0answers
163 views

Why would a default constraint be missing an index?

I have a query that looks at the sys.dm_db_missing_index_group_stats system view to determine what missing indexes there are for maintenance purposes. If I remove the filter from the query that ...
2
votes
0answers
279 views

Covering Index - Insert Performance

We are running some performance tuning on our database in SQL Server. The database in itself is pretty small with around 13 tables. But there are multi-level relationships between tables. When I try ...
2
votes
0answers
87 views

work/cache table issues, heavy inserts with heavy reads

we have a work table that stores availability of products. This table is updated with millions of inserts every night and medium amounts of inserts throughout the day. during the updates, we delete ...
2
votes
0answers
67 views

Index Maintenance

I have a query thats performing badly and has a RID lookup. There are already quite a few indexes on the table and in the query stats I can see they are used although some less than others and all ...
2
votes
0answers
112 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
1k views

SQL Server Database Engine Tuning Advisor vs DMV queries

What is the best way to tune the indexes on my production database? I normally use the following query : SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], ...
1
vote
0answers
51 views

Finding which queries used an index

I'm being asked to provide historical proof before I can maintain indexes - which is fine - over the past year I've kept a table full of index usage statistics for all the tables on the server. I ...
1
vote
0answers
60 views

How does mysql optimizer works?

I have two tables t_core_blueprints,t_ci. CREATE TABLE `t_core_blueprints` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_modified_on` ...
1
vote
0answers
32 views

Spatial index unused on nested set

I'm using the nested set architecture to store URL hierarchies in the following InnoDB table on MySQL 5.7. From my research, I've learnt that using a SPATIAL index will significantly speed up any ...
1
vote
0answers
53 views

Background index creation resource provisioning

I have a long running background index creation running in MongoDB. However, the CPU and RAM usage is below 30%. Is there a way to allocate more resources to the index creation process or is it ...
1
vote
1answer
45 views

Advice on fine-tuning query and index for performance

Currently my query is as below: select id_request,username_request from darkhast_follower where darkhast_follower.id_request != '9762952594' AND darkhast_follower.status =0 AND NOT exists (...
1
vote
0answers
318 views

Creating index on a big text column in postgres

We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE. The column is of type text and it is used ...
1
vote
0answers
358 views

mongodb aggregation query performance

We have a collection PurchaseOrder, it has embedded array of documents PurchaseOrderLines and another embedded document Warehouse. The record count is about ~4m for PurchaseOrder and each purchase ...
1
vote
0answers
28 views

Index to facilitate joining a table against itself to gather the last observation

I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as ...
1
vote
0answers
85 views

Testing efficiency of a BRIN index in Postgres

I have found many use cases in our OLAP queries for BRIN indexes, as they are much much smaller and have comparable speed in the same order of magnitude as BTREE indexes. Typically I use BRIN indexes ...
1
vote
1answer
285 views

What index can I create to help a query using multiple datetime columns and datetime functions in its where clause?

SQL Server 2016 I have the following query: SELECT [Id], [Descriptor] FROM [Networks] WHERE ( [ValidFrom] <= SYSDATETIMEOFFSET() AND SYSDATETIMEOFFSET() < COALESCE ...
1
vote
2answers
140 views

How to improve query performance?

I have a query that executes very often on our system and there is no option to change the query text. Are there any suggestions on what could be done? Any help would be appreciated. The query: ...
1
vote
0answers
55 views

What queries read my indexes and what queries apply inserts / updates to my indexes?

I'm looking indexes with 0 reads with SP_blitzindex, from there I am able to grab the indexes names that are probably not helping my system. Using the top query posted by Kendra: SELECT ...
1
vote
1answer
127 views

SQL Server Database Tuning Advisor - Temp index being dropped

Recently I ran a trace on our database so I could then plug it into Database Engine Tuning Advisor and see what queries needed to be improved and where there might be a real need for indexes. While ...
1
vote
1answer
136 views

Query Tuning and Index Tuning - SQL Server

I've been asked to improve the performance of the following query. I can't amend any existing Indexes but I can create new ones. The table structures can be found in this SQL Fiddle The row sizes of ...
1
vote
0answers
85 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 ...
1
vote
0answers
57 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 ...
1
vote
0answers
145 views

Date of last index rebuild for Mysql Database

is there any way we could check the last time when a database in MySql was rebuilt?
1
vote
0answers
155 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
0answers
112 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 = ...
1
vote
0answers
78 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 ...
1
vote
0answers
26 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
0answers
1k 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 ...
1
vote
0answers
211 views

Slow Tally Tables

I have a pain point with a relatively large application concerning 'tally tables', which are used for keeping track of the number of items against 'campaigns' (placed by a client), and 'publishers' / '...
1
vote
0answers
694 views

Dropping old and creating a new clustered index

I am wondering what is the best approach for dropping and creating a new clustered index on a table within SQL Server 2008. My primary concern is the length of time it will take to perform this ...
1
vote
0answers
756 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= DATEADD(...