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

learn more… | top users | synonyms

3
votes
1answer
265 views

Clustered Index - Query Performing Index Scan

Why does the below query perform an Index Scan rather than an Index Seek? I would have thought the Index would act as a covering index? CREATE NONCLUSTERED INDEX [IX_CustomerFileDate] ON ...
1
vote
1answer
685 views

MongoDB Keys and casting between types

This is more of a theoretical question: Let's say I have a collection of documents, my_collection: { "_id": ObjectId("52c679ac7a23e6225b62c51f"), "date": ISODate("2014-01-06T03:48:30.737Z") ...
4
votes
2answers
94 views

StackExchange clone: where should I add my indexes?

I'm creating an open source stack exchange clone and the following is my schema. What should I add indexes on for it to be optimal? Here is the schema in Rails format (SQL format below as well): ...
0
votes
1answer
163 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
77 views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
0
votes
0answers
528 views

Table Valued Function Very Slow

I have a TVF that when called takes 10 secs to run. However if I take the query inside that TVF, it takes only 1 sec. I have added correct indexes on this underlying query tables. In the Execution ...
9
votes
3answers
183 views

One index or two?

I have the following index created on a table in my database: CREATE INDEX [idx_index1] on [table1] (col1, col2, col3) The server is suggesting the following 'missing' index: CREATE INDEX ...
0
votes
1answer
258 views

SQL Server 2008 Timeout expired rebuild and reorganize index solve the issue

I have a large database with a file stream located on a different disk the server that hosts this database has a large number of another none related databases but the specifications of this server ...
1
vote
3answers
697 views

Low Cardinality of Index performing better than High Cardinality?

For testing purposes I have 2 Tables TableA and TableB in an mysql-InnoDB, both with the same columns id bigint not null auto_increment, depot smallint not null, custno bigint not null, range_from ...
-1
votes
1answer
303 views

How can I detect which indexes are being rebuilt offline and how long?

The rebuild of indexes takes appr. the same 55 min. with both "Do not rebuild indexes and "Rebuild indexes offline" "For index types that do not support index rebuilds" with "Keep index online while ...
0
votes
1answer
128 views

Which index types do not support online index rebuilds?

In SQL Server 2012 Enterprise Ed., which index types do not support online index rebuilds? and why? and which activity-operations can prevent online index rebuilds?
0
votes
1answer
392 views

What is blocking 'SELECT… WITH(NOLOCK)'?

While following BrentOzar's "How Much is Offline During an Index Rebuild?" I was curious to run: SELECT Quantity FROM [Production].[TransactionHistory] WITH(NOLOCK) while executing a rebuild ...
2
votes
1answer
181 views

NEWSEQUENTIALID() resets sequence on server restart

MSDN learns me that NEWSEQUENTIALID(): Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting ...
6
votes
2answers
696 views

Adding a Clustered index to a HEAP table on someone else's app

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% ...
1
vote
0answers
92 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 ...
6
votes
2answers
298 views

How to optimize NOT IN statement

Why does NOT IN performs INDEX SCAN instead of INDEX SEEK? Here's my current index on the table, CREATE NONCLUSTERED INDEX [idx_dmcasarms_CourseList_cDesc] ON [dbo].[courselist] ( [c_desc] ...
0
votes
1answer
94 views

Index Maintenance on SQL Server 2008 R2 Web edition [duplicate]

I've inherited of a couple of databases which indexes have never been maintained (rebuilt, reorganized). I'm using Ola Hallengren's Index maintenance script, I've used the following script to record ...
1
vote
1answer
297 views

MySQL query optimization with aggregate ORDER BY

I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items." The schema is as follows: ...
1
vote
1answer
124 views

Database Tuning Advisor Index Recommendations

How does DTA make an index recommendation? What is the technology behind that? If we can use same on our side then we can shorten the whole process of Tuning recommendation: creating trace file ...
0
votes
1answer
268 views

Index for ORDER BY on aggregate in MySQL

I'm working with the following query: SELECT *, SUM(entries_count) AS entries_count FROM leads WHERE promo_id IN (1, 2, 3) GROUP BY user_id ORDER BY entries_count DESC LIMIT 25 OFFSET 100; It's ...
0
votes
1answer
88 views

How to store values of all columns in an index in PostgreSQL?

As described here: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes indexes do not store the entire row data themselves. Is it possible to store the whole row ...
2
votes
2answers
124 views

Optimize query or model, to get an population overview in time

I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance. I wonder if there is a trick in my model, or MySQL query, to speed up the ...
2
votes
1answer
176 views

Creating indexes on AutoId and time column

My table structure is as follows Events( AutoId int identity(1,1) //Primay Key, clustered index EventTime datetime // non unique, non clustered index FileName nvarchar(1000) ...
0
votes
0answers
76 views

MySQL randomly refuses to use index

Mysql randomly refuses to use indexes for even simple queries. 1 example from slow-query log: # Time: 130904 12:19:59 # User@Host: cron[cron] @ [10.71.41.38] # Query_time: 58.488760 ...
2
votes
2answers
333 views

SQL Server 2008 R2 : A Possible indexing strategy for the given schema?

We have these four tables: Store ( row bigint, id uniqueidentifier, name varchar ) Products ( row bigint, id uniqueidentifier, storeID uniqueidentifier, productname ...
0
votes
1answer
272 views

SQL Server 2008 - Question about index behaviour

I have a general question about advanced issues regarding index behaviour. In short, roughly one year ago, we dynamically dropped and recreated a number of indexes with different filters, but using ...
4
votes
3answers
354 views

Using DTA vs. evaluating DMVs?

Currently I am confronted with a production SQL Server database where someone had added almost all missing index proposals from DTA. Form How to determine if an Index is required or necessary I have ...
0
votes
2answers
1k views

MySQL Cluster - Table is full [Error 1114] while still creating tables

I am trying to forward engineer a MySQL Workbench model with about 100 tables to a development single-node cluster and, while creating the 51th table, I end up getting a "1114 table is full" error, ...
3
votes
2answers
845 views

How do I know what indexes to create for a table

Is there a way I can figure out the best way to know which indexes to create for a table?
1
vote
1answer
159 views

Index method for very few updates and many inserts

I am using Postgresql 9.1 with *pg_trgm* extension. I need to create an index on a text-based field. I do not need full-text searches, I use ILIKE queries to make my searches. I will use pg_trgm but ...
5
votes
1answer
285 views

How can a fill factor of 0 or 100 be the same?

As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth. I can't correlate how fill factor of 0 and 100 can be the same! Am I ...
1
vote
1answer
612 views

Would adding indexes to my foreign keys improve performance on this MySQL query?

Consider the following query: SELECT `locations`.`id` AS `location_id`, `locations`.`address`, `locations`.`lat`, `locations`.`lng`, `tickets`.`status_id`, `customers`.`name`, ...
0
votes
1answer
74 views

Indexes and Optimizations for Logging System

I'm designing tables for a new log system and am having trouble figuring out what indexes I'll need (I'm a developer, not a DBA), and am also open to any suggestions/criticisms/etc. for making the ...
4
votes
1answer
1k views

How to properly index hstore tags column to faster search for keys

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column ...
5
votes
2answers
386 views

How to design index for faster deletes

I want to speed up following piece of code delete from ssn_sdo where (art_id=@art_id and skl_id=@skl_id and @level=0 ) or (art_id=@art_id and skl_id=@skl_id and @level=1 and ...
5
votes
2answers
168 views

Indexing strategy for dynamic predicate

Assume SQL Server 2012 Standard edition. My database has a table with 500 million rows. The table has about a dozen columns, none of which are very wide (some varchar(100)'s and some ints). The ...
4
votes
1answer
111 views

What is the difference between these two index declarations and do I need both?

I was examining a script with 'Display Estimated Execution Plan' and it made several recommendations for indexes to add to the database. I added them and the script did indeed run much faster. Now ...
0
votes
1answer
110 views

Am I wrong in table design or wrong in selected index when made the table?

I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
0
votes
1answer
47 views

MYSQL - Using simple inventory number convention as Primary Key

I have an inventory with three numbered groups like so: Group 1: 0-999 Group 2: e0-e999 Group 3: v0-v999 Can I simply make a one-column table to store this information and make that column the ...
1
vote
1answer
1k views

Varchar index - will hashing value make it faster?

I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
2
votes
2answers
980 views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
0
votes
1answer
115 views

Two identical MySQL tables, one not using indexes

I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data. Schema of one table CREATE TABLE `measuresHistory` ( ...
8
votes
2answers
1k views

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
4
votes
2answers
563 views

Execution plan results for sp_executesql suggests adding missing index for already existing index

I'm analyzing a query being run inside our system via sp_executesql. I am able to execute the query on the production database and include the actual execution plan. In the execution plan results ...
1
vote
1answer
51 views

Is it better to add an index and search via the index or add a unique constraint and let an insert fail?

I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job: mysql> DESCRIBE job_views; ...
1
vote
1answer
1k views

MySQL: logging queries which would execute without using indexes

I am trying to use log_queries_not_using_indexes = 1 to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, ...
3
votes
0answers
237 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 ...
0
votes
1answer
38 views
4
votes
1answer
2k views

What are the disadvantages if indexes are added on all columns?

I know, that it is not a good design to set too many indexes, but just for understanding the theory: What exactly are the disadvantages if you add an index on all columns in a table in MySQL? Are ...
0
votes
2answers
79 views

Indexing is not working

Following is the table and index information for which I have the queries at the end. Create Table: CREATE TABLE `food` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `page_no` int(11) DEFAULT ...