Tagged Questions

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` ( ...
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 ...
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 = ...
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 ...
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 ...
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 ...
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 ...
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
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). ...
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 ...
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 ...
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
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 ...
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, ...
2
votes
1answer
66 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
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
118 views

INDEX Creation Advice Oracle

I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id. The attributes date, and entity_id would make a unique pair. ...
0
votes
0answers
143 views

Why this query takes 500 seconds to complete even though I got all the compound index right?

I tried this query db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { ...
5
votes
2answers
474 views

How can I improve the speed of a query on a 20 million+ row table?

I have a query that is used for getting internet traffic statistics of certain IP addresses. There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
7
votes
2answers
345 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, ...
3
votes
1answer
192 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
2
votes
2answers
337 views

Reorganize Indexes T-SQL

I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on ...
2
votes
1answer
95 views

Are two indexes needed?

Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter. Which indexes should I use? Should I have two ...
1
vote
1answer
455 views

Index Strategies on Text or NVARCHAR(MAX) Fields

I have the following query (simplified for question) I'm trying to speed up for a read only DB ... SELECT [sysid] ,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101) ,[From]=[from_addr] ...
0
votes
1answer
95 views

Creating Indexes to optimize query

I have this query and I want to increase its performance by adding appropriate Indexes. DELETE FROM MYTAB1 WHERE MYID1 IN ( SELECT MYID2 FROM MYTAB2 ); I am not familiar with the ...
1
vote
1answer
173 views

Analyzing TCB Index Status for DB2

The Following is the TCB index that I could get using a command for my DB2 database. I don't know how to analyze it. Which columns are important? What do they mean? TCB Index Stats: Address ...
5
votes
2answers
2k views

What is the difference between ALTER INDEX and DBCC DBREINDEX?

Is the only difference between ALTER INDEX [index_name] on [object_name] REBUILD with (ONLINE=OFF, FILLFACTOR=90) and DBCC DBREINDEX([dbname], 90) just that the DBCC command will reindex all ...
3
votes
1answer
718 views

Oracle not using index when > used in where clause

We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date. Because there is no end clause to the date range, oracle ...
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 ...
8
votes
2answers
2k 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 ...