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 ...
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
307 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 ...
-1
votes
1answer
200 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 ...
2
votes
1answer
164 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
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 ...
5
votes
2answers
383 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 ...
0
votes
1answer
104 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 ...
4
votes
1answer
157 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 ...
2
votes
0answers
63 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 ...
6
votes
2answers
609 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% ...
2
votes
1answer
113 views

Does updating a partially indexed field add or remove it from an index in Postgres

We can create a partial index for a field in Postgres. I am looking at using this to stop rows which are archived from being in an index on a created_at field: First we create the index: test=# ...
1
vote
1answer
171 views

What would be a optimal index for this query in mysql

I'm trying to optimize this query. SELECT * FROM sales_reps WHERE account_id = 1 AND (state = 'enabled') AND (clients >= 5 OR revenue >= 10000) ORDER BY name, platform, client_group, ...
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
4k views

Primary key vs. unique index: performance difference with foreign keys?

Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following: A primary key A unique ...
11
votes
2answers
4k views

How to know when/if I have too many indexes?

Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement..."). From my understanding every added ...
4
votes
3answers
348 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 ...