The process of determining which indexes are useful and which are not.

learn more… | top users | synonyms

0
votes
2answers
1k 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
149 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 ...
5
votes
1answer
238 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
502 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
72 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
1k views

How to properly index hstore tags column to faster search for keys

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column ...
5
votes
2answers
385 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 ...
4
votes
1answer
111 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
157 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
47 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
110 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
206 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
91 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 ...
0
votes
1answer
109 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
1k 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
1answer
50 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; ...
1
vote
1answer
972 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
219 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
38 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 ...
4
votes
1answer
1k 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 ...
3
votes
1answer
146 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 ...
13
votes
3answers
2k 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 ...
2
votes
1answer
503 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
164 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
786 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
721 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
573 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 ...
6
votes
1answer
166 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
65 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
79 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
94 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
2k 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
79 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
2answers
683 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
56 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
332 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, ...
2
votes
1answer
485 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). ...
6
votes
2answers
2k 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 ...
1
vote
1answer
690 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 ...
3
votes
2answers
755 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

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 ...
1
vote
2answers
140 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: ...
2
votes
2answers
109 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 ...
5
votes
2answers
899 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
1answer
247 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 ...
1
vote
2answers
1k 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
160 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 ...
3
votes
1answer
356 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
63 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, ...
6
votes
2answers
650 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% ...