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

learn more… | top users | synonyms

3
votes
1answer
25 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
11 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 ...
5
votes
3answers
67 views

Slow query performance due to temporary file?

Here is the query: SELECT "products".* FROM "products" WHERE (status > 100) AND "products"."above_revenue_average" = 't' AND ("products"."category_id" NOT IN (5)) ORDER BY ...
2
votes
1answer
50 views

Index is seeked and scanned - is there a risk for seek to fail after modification?

I have an index that has been seeked 230.000 times and scanned 130.000 times. In order to improve the queries that have scanned, because they use an additional column I guess, I would like to change ...
7
votes
1answer
92 views

Clustered Index Help, where did i go wrong?

I was asked to review a very busy table and identify any areas of improvement. I have very limited power to change the table just the indexes. Table information 240 columns ~5 million rows Read ...
2
votes
1answer
37 views

Index usage and update statistics do not correspond?

In order to discover unused and wrong indexes in sql server I went through the following article: https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/ It gives two queries ...
6
votes
1answer
76 views

Ola Hallengren Index Script not Reindexing

First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on. I have Ola's script set as @Databases nvarchar(max) ...
1
vote
0answers
95 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' / ...
6
votes
3answers
119 views

Can PostgreSQL use nulls in its indexes?

I've been reading this book which says that The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this ...
10
votes
4answers
458 views

Should the index on an identity column be nonclustered?

For a table with a primary key (PK) on an identity column, it will be clustered by default. Could it be better as nonclustered? The reason is other indexes will be created for queries. A query which ...
2
votes
1answer
50 views

Ways to Reduce Database Size and possibly improve performace. Untouched enviroment.

I am new to an environment. The Databases are virtually untouched by a seasoned DBA. I am looking for ways to reduce DB size and possibly improve performance. Unfortunately, I only have WEb edition ...
1
vote
1answer
45 views

How to optimize the sort in Postgres query

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance. I've tried converting the ...
1
vote
3answers
63 views

Intentionally corrupting an index in PostgreSQL

I need to know if there is any method for intentionally corrupting an index in PostgreSQL. I have been searching for it for a long time but never seen anyone mentioning such an act.
2
votes
1answer
56 views

Row with latest timestamp

How to get the row with the latest value in a TIMESTAMPZ column? Is an index needed? Does an index change the strategy? Would behavior vary by database (I'm using Postgres 9.4)? My app records data ...
0
votes
2answers
21 views

Separate indices for the field I want to get the MAX() and the ones in the WHERE clause

My customer's database has a gigantic table (hundreds of millions of records) and a SELECT that, by cumulative use, is eating a huge chunk of processing time. Each exec takes almost a minute in ...
1
vote
1answer
57 views

Index based on another index [closed]

Assume a mysql table with columns A, B, C, ... . There are one composite index based on columns A+B+C. Combination of values A, B and C is unique, so this index forms the primary key. Now, a second ...
2
votes
1answer
164 views

Excessive CPU use in vendor SQL Server DB

Background: I've got a SQL Server 2012 DB provided by a vendor so modification of queries and tables is limited. We do own the DB though, so we can add and maintain indexes. Indexes haven't been ...
1
vote
1answer
26 views

Index design question

This query runs for 60 seconds: SELECT mychecklis0, .... FROM dbo.MyChecklistStatus mychecklis0_ WHERE mychecklis0_.AppFormSubSectionId=@P0 AND mychecklis0_.ProfileId=@P1 There is an index created ...
3
votes
0answers
52 views

How to do Statistics in Partial Indexes of PostgreSQL efficiently?

This answer motivated think again my following tables but I am still uncertain how to store statistic data for quick selection. I think it is well done with partial indexes because only a subset of ...
1
vote
1answer
67 views

mysql query choosing an incorrect execution plan

I have a primary with an dependant subquery as a where condition that is choosing a wrong execution plan, and therefore is running for ever. Main query: SELECT rtr.id AS id156_, ...
1
vote
2answers
36 views

Reorganize Index Task [closed]

Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance? Is this an adequate step of triage for degraded application/query performance?
1
vote
1answer
50 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
3answers
114 views

Index optimization and cardinality

I'm by no means a DBA expert. Rather, a Magento developer having to troubleshoot some slow queries. I'll jump right in -- here's my attempt to EXPLAIN the troublesome query: EXPLAIN SELECT ...
0
votes
0answers
71 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 ...
5
votes
2answers
234 views

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS ...
4
votes
1answer
218 views

Sudden poor SELECT performance on a large table with existing indexes

My company uses a software suite in which the only thing I can truly modify is the database. We've always had performance issues (mostly hardware problems that we hope to resolve soon), but lately ...
1
vote
1answer
39 views

Optimize view (and underlying table) for averaging timestamps into hours

I have this table: CREATE TABLE spp.rtprices ( "interval" timestamp without time zone NOT NULL, rtlmp numeric(12,6), rtmcc numeric(12,6), rtmcl numeric(12,6), node_id integer NOT NULL, ...
1
vote
1answer
80 views

Help on deciding how to pick Correct Index

I would like to ask for some help to create correct indexes for this query: SELECT DISTINCT D.TypeID FROM dbo.Sizes AS S WHERE EXISTS ( SELECT 1 FROM #PermissionsTable AS PT ...
0
votes
1answer
100 views

Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)

We have a huge query on an Informix database that is not performing well. The query is doing a join of 9 different tables. Maximum row count is just 40,000 rows. The query also has 9 sub-queries, and ...
0
votes
1answer
42 views

How to determine if an Index is uselss in Percona MySQL?

Using Percona Server 5.5 I've a table which stores user activty (lets call it user_activity). It was created quite a few years ago when I had only a little bit of knowledge about database ...
3
votes
2answers
81 views

Mysql Index for concat select

For Below Query SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE TRIM(LEADING 0 FROM card_no)='2948' I've index on date card_no date,last_entry ...
1
vote
1answer
101 views

Index to filter a view before joining the underlying tables [closed]

Using SQL Server 2008 R2 Suppose you have a query like the following select top 1 * from view_cms_user where UserId = 1234 or UserPasswordRequestHash = 'anxazno121o3n' view_cms_user is basically: ...
0
votes
1answer
43 views

What is the risk of doing a rebuild to my indexes in sql server 2012?

What is the risk of doing a rebuild to my indexes in SQL Server 2012? I have to do a REBUILD, but I don't know what are the risks . The server is running and a lot of information is going in and out. ...
0
votes
1answer
39 views

Indexes in a big table. (About 400 mil of rows)

I have a little deal. I have this table in a SQL Server 2012 Standard edition database. The table contains about 400 mil of rows. CREATE TABLE [dbo].[prodeje] ( [id] [int] IDENTITY(1,1) NOT ...
0
votes
0answers
33 views

PostgreSQL: raise error if planner needs to do an sequence scan

I want to check if our code does use only indexed columns. I think it does, but I want to be sure. How can I configure/modify PostgreSQL to raise an exception if it needs to do an sequence scan? It ...
1
vote
0answers
75 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] >= ...
0
votes
2answers
62 views

SQL Server 2005 query with multiple conditions, some indexed, some not

I have a table with 50 million rows. It is indexed on zip. A query Select * from table where zip = '12345' returns 20,000 rows in about 2 seconds When I add an additional condition it jumps to ...
3
votes
1answer
294 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"; ...
1
vote
0answers
64 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
1answer
150 views

Clustered index on a column with low selectivity

I have a table with a lot of columns. Two lookup functions are performed: 1) lookup by using column name (this happens rarely, maybe twice a season), and 2) lookup by column date. The column name has ...
1
vote
1answer
64 views

Rebuild vs Reorganize [duplicate]

I currently have a maintenance plan that is reorganizing indexes everyday and I would like to know if you reorganize an index everyday would you ever need to rebuild the index? If so, why?
0
votes
0answers
53 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 ...
2
votes
0answers
78 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 ...
1
vote
1answer
73 views

Postgres - Insert performance with indexes

We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow ...
0
votes
0answers
27 views

When I can DROP an index safely? [duplicate]

I would like to get rid of unused indexes in my database. I have identified many indexes that are never used (seeks, scans and lookups are 0), and are being updated during DML operations. I used ...
0
votes
1answer
43 views

One Non-Clustered Index that covers every possible combination of predicates?

Say we have a table against which a number of different queries can be executed and each query has its own combination of predicates. So, for example, if we have a table with columns A, B, C, etc we ...
4
votes
1answer
94 views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? ...
1
vote
1answer
224 views

Poor query performance due to recheck condition on index scan

We are testing the performance of metadata when stored in JSONB compared to a traditional EAV catalogue (commonly used in biomedical scenarios), using a PostgreSQL 9.4 server. I get a problem when ...
0
votes
0answers
17 views

Index creation issue with MySql and Linux

MySQL Server database is installed on RHEL5.5 and db version is 5.6.20 Community Server. In MySQL database we have a table and it has 11,144 records. I am altering the table and adding index to it. ...
2
votes
2answers
157 views

Updating indexes and stats script

I am having the following errors with the script below Msg 102, Level 15, State 1, Line 44 Incorrect syntax near '?'. Msg 319, Level 15, State 1, Line 47 Incorrect syntax near the keyword ...