0
votes
0answers
30 views

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
1
vote
1answer
18 views

Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market: WITH markets AS ( ...
-1
votes
2answers
30 views

Indexing into two “buckets”

I'm creating a database with PostgreSQL that has an integer field called "delay". All of my queries on this column will be checking if the value of this column is less than or equal to 0, or greater ...
3
votes
1answer
74 views

Does sp_indexoption cause index rebuilds?

I want to switch AllowRowLocks=ON on a number of indexes. Some of those indexes are on large tables, and I don't really want to rebuild or reorganize the index. It is not clear from the MSDN article ...
0
votes
1answer
27 views

select where index + other field

Learning indexing I have made an index, consisting of usergroupid and birthdate I understand that queries will then benefit from indexing when searching usergroupid and/or birthdate But what ...
0
votes
1answer
38 views

MySQL: How index speed up query execution

I have read multiple articles about MySQL indexes. Some of the important pages are below: http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html ...
6
votes
2answers
86 views

Index that is not used, yet influences query

I've got a PostgreSQL 9.3 table with some numbers and some additional data: CREATE TABLE mytable ( myid BIGINT, somedata BYTEA ) This table currently has about 10M records and takes 1GB of ...
0
votes
1answer
69 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
132 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
38 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
136 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
42 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
37 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
46 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
147 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
74 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
100 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
37 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
74 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
63 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
111 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
107 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
45 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
171 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
117 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
361 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
90 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
304 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
93 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
631 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
54 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
179 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
86 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
225 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
117 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
222 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 ...
8
votes
2answers
3k 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
254 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
892 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
132 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
209 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
411 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
151 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
100 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` ( ...