Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [index-tuning]

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

10
votes
0answers
567 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
0answers
260 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
21 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
605 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
0answers
70 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 ...
2
votes
0answers
48 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
0answers
155 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
0answers
106 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
134 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
76 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
92 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
788 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
349 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
158 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
269 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
65 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
15 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
29 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
0answers
96 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
141 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
27 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
55 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
0answers
49 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
0answers
57 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
51 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
117 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
127 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
103 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
75 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
196 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
677 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
706 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(...
1
vote
0answers
67 views

Multiple SQL Server Indexes, which ones to keep?

I am looking at my database that several people have worked on. A particular table is responding slowly when queried on a certain join. I notice some of the parameters of the join are not indexed. ...
1
vote
0answers
378 views

Rebuild/Re-organize index and Update statistics in SQL server

For many of the sql servers we have seen that the SQL agent Maintenance job first runs the re-indexing and later as a second step it update stats with full scan. I want to figure out if auto update ...
1
vote
0answers
419 views

Why is Update Query Slow with correct Indexes?

I have a query that updates a temp table. There are 2 Filtered indexes (by date) used in this query. However, the update take 8-10 mins to run. I am struggling to find out why it is so slow. There is ...
1
vote
0answers
272 views

MySQL not using multicolumn index on self join

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows. /* Count all occurrences between start and end site id of a given speed ...
1
vote
0answers
165 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
0answers
123 views

Index Tuning Question

I have the following query: SELECT T1.ITEMID, T1.ITEMTYPE, T1.PURCHMODEL, T1.HEIGHT, T1.WIDTH, T1.SALESMODEL, T1.COSTGROUPID, T1.REQGROUPID, T1.EPCMANAGER, T1....
1
vote
0answers
109 views

Can't get rid of filesort on joined query sorting on second table

For some reason, this query: EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5; says it ...
0
votes
0answers
43 views

Slow query with order by join table

I have a SQL with multiple join left, because some data is empty. However, I need to sort the data using a string column from a join table. I thought about creating an index for this table, but this ...
0
votes
0answers
27 views

Update stats as default or with a sample value

I have been reading many blogs on this and cannot come to a conclusion on update stats for VLDB;s On SQL server 2012Sp4 and 2014Sp2 we have databases over 15 TB with 4-5 tables covering most of the ...
0
votes
0answers
63 views

Helpful nonclustered index improved the query but raised logical reads

I attempted to improve the following query that is getting executed continuously: SELECT TOP(?) Schema1.Object1.Column1, Schema1.Object1.Column2, Schema1.Object1.Column3 FROM Schema1.Object1 ...
0
votes
0answers
58 views

What index type to use for a log

I have a table in which I will store messages (as in chat messages). The table will be appended to a LOT, rows being inserted as messages fly by, much more often than entries will be updated. Older ...
0
votes
0answers
25 views

Index Help - Plan has 2 index-scans, query is slow & high-wait

https://www.brentozar.com/pastetheplan/?id=rJpfNCsJl The query above is identified as "expensive" and "slow" by monitoring tools, and I feel it could use some indexing help, but I'm not completely ...