The process of determining which indexes are useful and which are not.
7
votes
1answer
266 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 ...
4
votes
0answers
53 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
65 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 ...
0
votes
1answer
15 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 ...
2
votes
0answers
42 views
What is Transaction Path Analysis?
I came across the term Transaction Path Analysis which I am not familiar with. A Google search resulted in only a few results, most of which seem to assume everyone knows what it means. From these my ...
1
vote
0answers
58 views
Can't get rid of filesort on joined query sorting on second table
For some reason, this query:
EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id
WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5;
says it ...
1
vote
1answer
84 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 ...
1
vote
2answers
490 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
55 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` (
...
7
votes
2answers
345 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
253 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
26 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
49 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
45 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
55 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
34 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
124 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
83 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 ...
2
votes
1answer
81 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
86 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
109 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
217 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
114 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
110 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
34 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
67 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
56 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
245 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
142 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
38 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
179 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
113 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
203 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
194 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
144 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
99 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
63 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
107 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
138 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
163 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
353 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
149 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
206 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
403 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
106 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
205 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, ...
