0
votes
1answer
56 views

When to rebuild and when to reorganize indexes [duplicate]

I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query : SELECT ...
1
vote
1answer
69 views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
0
votes
0answers
31 views

Rebuilding an index not working? [duplicate]

I noticed that when I right click manually on an index and choose "Rebuild" or "Reorganize", its not giving any indication that its doing so, then I click on properties again for the same index, I see ...
1
vote
0answers
32 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: ...
2
votes
1answer
108 views

How to get rid of clustered index scan?

I have a query that was generated by C#/Linq-To-Entities code. As with this kind of query, it looks seriously ugly and I doubt it would help to include it here. But I have run it through a query ...
0
votes
1answer
35 views

How to index for multiple inequality WHERE search conditions?

I am thinking on the problem, if there is a multiple columns on which search must work as fast as possible. For example, see the table TEST(COL_A, COL_B) with a (tree) index TEST_IDX(COL_A, COL_B). ...
1
vote
1answer
36 views

Can an index refer to only one or a subset of possible values?

I am using Postgres. The table looks something like this: tran_id SERIAL PRIMARY KEY, account_id integer NOT NULL CONSTRAINT fk_account_id REFERENCES base.account(account_id) ON DELETE CASCADE, ...
0
votes
1answer
41 views

NTEXT,Indexing, and lob logical reads

We have an query that would benefit from a covering index but one of the columns that would be returned is a NTEXT data type. I know this can't be added to the index and I also know it would be good ...
5
votes
2answers
132 views

Any advantage of having a second index with included columns on a primary key?

I'm reviewing our database and noticed that on a particular table we have the following indexes: Table: Col1 INT IDENTITY(1,1) Primary Key Col2 INT ... about 15 more columns .... ColN ...
2
votes
1answer
72 views

Does Index Intersection work with Included Columns?

I've been asked by a report designer whether it is acceptable to add a new index to a table to speed up a particular report. The index would be on a single column, CreatedDate, but would have ...
1
vote
4answers
99 views

Profling for Index Maintenance

I am wanting to perform some index maintenance, in particular we have a poor performing query against a particular table. Is it a sensible strategy to profile on RPC:Completed, SP:StmtCompleted and ...
1
vote
0answers
47 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 ...
0
votes
0answers
33 views

MySql - multi-column index using only null values from one column

I have a Innodb table with a index that looks at 3 columns. ColA is integer and always has a value. ColB is varchar and always has a value. ColC is datetime and sometimes has a value (NULL). My ...
2
votes
1answer
69 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
62 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 ...
3
votes
2answers
103 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
90 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 ...
0
votes
1answer
39 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
143 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
111 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 ...
6
votes
1answer
355 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
80 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 ...
2
votes
1answer
199 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 ...
4
votes
2answers
90 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
410 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
49 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?
9
votes
3answers
178 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
76 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
185 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 ...
0
votes
1answer
85 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
116 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
187 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 ...
7
votes
2answers
2k 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 ...
0
votes
1answer
239 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 ...
0
votes
2answers
814 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, ...
1
vote
1answer
122 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 ...
4
votes
1answer
199 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
361 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
70 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
109 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 ...
5
votes
2answers
150 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 ...
0
votes
1answer
46 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 ...
0
votes
1answer
97 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` ( ...
1
vote
1answer
933 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 ...
4
votes
1answer
154 views

After Rackspace server creation, PostgreSQL query planner doesn't work as expected

We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
6
votes
1answer
163 views

Is there any reason to use index for very small tables (up to 1000 rows)?

During application development I have a lot of tables that store "small" amount of data (usually 10-40 values, id + value and sometimes type) which hold attributes for "objects", like fresh/rotten, ...
2
votes
0answers
62 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
1answer
1k views

Unique insert performance: Primary Key vs Unique Index

I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records. What's the fastest way to insert into the table, by keeping the domain unique ...
0
votes
2answers
77 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 ...
1
vote
1answer
55 views

MySql include partition key in index?

Please look at the following Mysql table: http://pastebin.com/b0NDSbdz I've partitioned the table by sent_at and I'm making sure that most of the queries pass sent_at. do I need to include sent_at in ...