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

learn more… | top users | synonyms

2
votes
1answer
79 views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
0
votes
1answer
49 views

Which should I use on my DB? - Clustered Index or Non Clustered Index or Both [duplicate]

I just need advice on how I am going to optimize my database. | Date | Time | Area | Block | Data1 | Data2 | Data3 | DataN | I have that format of tables on my database. Each tables has 30-days of ...
3
votes
3answers
217 views

Join table on some conditions

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity. This are the message columns : id sender_id receiver_id body activity_id I'm ...
0
votes
1answer
65 views

How to index WHERE (start_date >= '2013-12-15')

I have a table named items, and inside the table there is a some_date column, with date as the datatype. I was wondering how can I index the some_date column with PostgreSQL SELECT "items".* FROM ...
1
vote
1answer
35 views

Index Tuning on Joined Query

I have 2 tables (tblA and tblB) joined by ReportID key. This key is a Primary Clustered key on tblA, and is a FK on tblB. I have created a Non-clustered index on UserID and UserDate in tblA. I have a ...
2
votes
2answers
35 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
3
votes
2answers
126 views

Compound Index on 43 Million PostgreSQL table

This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order) Rather than overload that question, I figure I can sharpen and limit my ...
1
vote
1answer
133 views

Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

Using PostgreSQL. The primary driver of this is that while no reservation will exist without a date and time, a user can search for and monitor reservations using a time or date or both. With a ...
1
vote
1answer
136 views

MySQL: does Index Merge Sort-Union access algorithm use temporary table?

My question is about index_merge optimization in MySQL. I have a query with a set of range conditions connected with OR. EXPLAIN of the query shows that index_merge method is used and the Extra ...
0
votes
1answer
63 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
3
votes
1answer
224 views

2 B-tree indices OR 1 GiST index on tsrange — which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
2
votes
2answers
126 views

Most performant way to fetch last record

I have a Postgres table Prices with the columns price (Decimal) product_id (Int) Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table ...
0
votes
2answers
69 views

How should I structure my time based index?

So I have this table in SQL Server 2008. Let's just say it's called TimeSheets. Now, that table has two columns I am concerned with. TimeSheetDate and UserCustomerKey. TimeSheetDate is a ...
0
votes
0answers
29 views

What Index can I apply to a GROUP BY, MIN() and WHERE MySQL Query

I have the following table: CREATE TABLE `wp_prize_draws_log_copy` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `entry_date` date NOT NULL, `entry_time` datetime ...
0
votes
1answer
54 views

Why is the index not used?

When I run the following querys in MySQL: SELECT count(*) FROM Inventory AS i WHERE i.lastModified > 1389920146804 or SELECT i._id FROM Inventory AS i WHERE i.lastModified > 1389920146804; ...
6
votes
1answer
370 views

Why is multiple independent index always better than concatenated index [closed]

Index Merge: Combining Multiple Indexes It is one of the most common question about indexing: is it better to have one index for each column or a single index for all columns of a where ...
1
vote
1answer
106 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
3
votes
1answer
532 views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...
0
votes
2answers
518 views

MYSQL - Why does this SELECT get slower the further down the table?

My table: CREATE TABLE `wp_users` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_login` varchar(60) NOT NULL DEFAULT '', `user_pass` varchar(64) NOT NULL DEFAULT '', ...
3
votes
1answer
212 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
528 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): ...
1
vote
2answers
1k views

Adding index to large mysql tables

I have a table | base_schedule_line_items | CREATE TABLE base_schedule_line_items ( id int(10) unsigned NOT NULL AUTO_INCREMENT, installment int(10) unsigned NOT NULL, on_date date ...
0
votes
0answers
65 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
407 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
181 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
219 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
votes
1answer
259 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 ...
1
vote
3answers
546 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 ...
0
votes
1answer
112 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
312 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
153 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 ...
9
votes
1answer
368 views

Poor query performance

We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do ...
4
votes
3answers
2k 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 ...
6
votes
2answers
294 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 ...
0
votes
1answer
152 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
266 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
116 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
228 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
86 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
121 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 ...
1
vote
2answers
279 views

How to improve performance if index is not Most SELECTIVE?

As per suggestion index must be most selective to improve the performance. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows The ...
2
votes
1answer
170 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
70 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 ...
9
votes
2answers
4k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
2
votes
2answers
312 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
264 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 ...
1
vote
1answer
110 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
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, ...