The process of determining which indexes are useful and which are not.
1
vote
1answer
58 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 ...
5
votes
1answer
221 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
1
vote
2answers
476 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
51 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` (
...
6
votes
2answers
309 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 ...
3
votes
2answers
250 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
25 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;
...
0
votes
0answers
44 views
one vs two column index difference when doing JOIN query?
Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query :
SELECT * FROM alfa
JOIN beta on beta.id = ...
1
vote
1answer
41 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
48 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
33 views
Can I rely on the order of colums on an index generated by Database tuning advisor
I got the following recommendation on a table:
CREATE NONCLUSTERED INDEX
...
3
votes
1answer
110 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
60 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 ...
3
votes
1answer
77 views
How can I determine what is using a particular resource (table, view, or function, etc) on my SQL Server?
I've been looking for tables that could use better indexing. I've created a stored procedure that provides a list of tables that have no indexes other than a primary key:
CREATE PROCEDURE ...
1
vote
1answer
59 views
Best practice around sys.dm_db_missing_index_group_stats in SQL Server
I have a fairly simple query using sys.dm_db_missing_index_group_stats that identifies missing indexes in my SQL Server database. This is pretty commonly used. My question is how do I identify which ...
4
votes
1answer
78 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 ...
0
votes
1answer
89 views
Is Reorganizing Indexes required after ShrinkDatabase or can I just reogranize index with fragmentation more than 10%?
I am a new DBA to a SQL 2005 production Report server. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on ...
5
votes
2answers
184 views
Finding stored procedures with missing indexes
How to scan for stored proc that need indexes to be created.
Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script ...
1
vote
1answer
104 views
Optimizing an audit table for inserts and deletes
I'm trying to identify the best way to design a table for audit purposes. Basically I log several last events for many users in one table. There's a limit on the number of records for each user. The ...
5
votes
1answer
107 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
32 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 ...
4
votes
1answer
66 views
Index Evaluation
I have been doing some index evalaution on a table and this is the first time I have ever looked at how well indexes are performing so and would like some advice on a certain index.
The non ...
3
votes
1answer
51 views
What is a suitable index for this query?
Below query is taking long time - around 540 seconds.
The table did not have any indexes. I created indexes on columns in the WHERE clause and other fields but the query is not using these indexes.
...
2
votes
1answer
220 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 ...
1
vote
2answers
130 views
What am I doing wrong with the Database Tuning Advisor?
My background: I'm a dev/architect, not a DBA. Sorry!
So we have a ~400 table 75GB database. I ran Profiler for ~24 hours ("Tuning" template minus LoginName) and have ~7GB of usage recorded in a ...
1
vote
1answer
37 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 ...
1
vote
2answers
171 views
Query Performance Tuning on a huge table
I have the following table Transactions. The table looks like this:
CREATE TABLE [dbo].[Transactions](
[TransactionID] [bigint] IDENTITY(1,10) NOT NULL,
[PlayerID] [bigint] NOT NULL,
...
1
vote
1answer
98 views
Use GIN to index bit strings
I'm trying to extend PostgreSQL to index bit strings up to 1000 bits. (These bit strings are created by quantization of high-dimensional vectors, so for each dimension up to 4 bits are assigned). ...
4
votes
2answers
180 views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about 1MM ...
0
votes
0answers
30 views
Algorithmic order of table indexes for table operations [duplicate]
Possible Duplicate:
Algorithmic order of table indexes for table operations
Duplicate of this question on stackoverflow
I have not been able to find a clear indication of the impact table ...
3
votes
2answers
191 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
129 views
Which stored procedures are using a specifc index
I've been reviewing the indexes in our databases (adding missing indexes and removing bad ones). I stumbled on an index I created months ago which looks way to good to be true. Since the last server ...
1
vote
1answer
96 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, ...
1
vote
1answer
61 views
Performance impact of including result column in indexed columns
I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of:
texelid ...
2
votes
2answers
92 views
Optimizing index creation
One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns.
An example case would be:
table1
...
1
vote
2answers
106 views
How to optimize this query?
Query:
Select *
from `t_event`
where `create_user_id`=7
and (`event_create_date`)=('2012-12-18 00:00:00')
and `event_type_cd`=11
and `domain_id` =602
and `job_id` =1
limit 1
Table structure:
...
1
vote
1answer
131 views
Simplify and optimize a complex query
Our site has a chronological event feed that uses MySQL. We have noticed that some of the queries tend to run for quite a bit longer than others. For example, some queries for our heaviest users take ...
5
votes
2answers
151 views
Cardinality rule for bitmap indexes
The Oracle documentation includes the following advice:
A bitmap index should be built on each of the foreign key columns of
the fact table or tables
In that reference, there is even a bitmap ...
1
vote
2answers
318 views
Best way to organize compound indexes on partitioned table (date by range)?
I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date ...
2
votes
3answers
148 views
Determining if indexes are redundant
I have a table with the following structure:
Id (int identity PK)
FooId (int FK)
BarId (int FK)
QuxId (int FK)
Other fields
The following indexes have been defined:
PK (Id), clustered
Index1 ...
2
votes
1answer
204 views
Is staggering reindexing jobs a good strategy?
I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes.
...
5
votes
1answer
397 views
SQL Server 2008 Maintenance Plan Failure
Instead of using my old school index rebuild script I had decided to use 2008's built-in Maintenance Plan to do a simple Rebuild Index task.
The details are:
Connection = Local server connection
...
3
votes
2answers
105 views
index maintenance strategy if few insertion points relative to # of rows
In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless ...
3
votes
1answer
190 views
Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View?
My initial question was How does someone determine if it is a good idea to index a view?.
Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, ...
0
votes
1answer
54 views
Is it any potential problem with having 2 constraints suppored by one index in Oracle?
I have the following table :
CREATE TABLE action (action_id INT NOT NULL,
action_type_id INT NOT NULL,
action_date DATE NOT NULL);
CREATE INDEX IDX_ACTION_ACT_TYPE_ACT ON action(action_id, ...
4
votes
2answers
764 views
How I should fix the following update statement to make it run faster?
I have the following DML:
UPDATE rc_usutb011 u
SET cod_ant=(
SELECT cod_hst
FROM rc_pdptb101 p
WHERE ocip_hst=ocip_ant
AND p.sector=u.sector
);
The explain plan ...
6
votes
2answers
268 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% ...
3
votes
3answers
128 views
What is a good guideline for sampling for statistics updates
Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent.
Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most ...
7
votes
2answers
346 views
Filtering data ordered by rowversion
I have a SQL table of data with the following structure :
CREATE TABLE Data(
Id uniqueidentifier NOT NULL,
Date datetime NOT NULL,
Value decimal(20, 10) NULL,
RV timestamp NOT NULL,
...
7
votes
1answer
386 views
What factors go into an Indexed View's Clustered Index being selected?
Breifly
What factors go into they query optimizer's selection of an indexed view's index?
For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
