A database structure that can improve the speed of queries at the cost of disk space and slower inserts/updates. It stores a copy of one or more columns but structures the data differently to allow for faster access.

learn more… | top users | synonyms (2)

2
votes
1answer
822 views

How to store GPS coordinate and search places in a radius from a NoSQL DBMS (like DynamoDB)

My team need a DBMS like DynamoDB to store large amount of data, principally places and coordinates. I've considered to use some GIS-based DBMS (like PostGIS) with an index on the POINT, but DynamoDB ...
1
vote
5answers
3k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
7
votes
3answers
1k views

Creating an index in both asc and desc directions

Over the past few weeks I've been raging against an old Firebird database. This database is crappy for all sorts of reasons, but one thing I noticed was that every single field of every single table ...
2
votes
1answer
1k views

Creating Bitmap Indexes on a Materialized View

I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned: Also, remember that bitmap indexes are only suitable for static ...
3
votes
1answer
798 views

Will an index that is a partial key of the begining of another index perform any better than the longer index?

If I create an index that is a partial key of the begining of a second index, will SQL Server get any significant gains retreiving the results from the simpler index, when the search criteria matches ...
2
votes
2answers
73 views

Are these extra Index fields needed?

I've designed a table as below for users who register a quiz: CREATE TABLE `registered_quiz` ( `rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `user_id` smallint(5) unsigned NOT NULL, ...
2
votes
0answers
37 views

Can I read data from a 'hash field' in a legacy Ingres database?

I have been asked to read and present data from the 'hash fields' of an Ingres 2.0 database, which are apparently multiple pieces of data stored together in a single field in various combinations. I ...
2
votes
1answer
757 views

Disabling Indexes and Auditing for large data import

Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are: 1. Is it ...
2
votes
1answer
1k views

Is Index causing timeouts?

Recently, while fine tuning the db for better perf, I changed a couple of indexes on tables which were hit very often. I am using the following query to keep tab on index stats SELECT DISTINCT ...
1
vote
1answer
2k 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] ...
7
votes
5answers
6k views

Improve sort performance in PostgreSQL?

I've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the ...
0
votes
1answer
170 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
1k views

Is it possible to add index to mysql with multiple column to optimize order by performance?

# I have a simple table create table t1 (c1 int, c2 int); # I want to do the following sorting select * from t1 order by c2 desc, c1 desc; select * from t1 order by c2 asc, c1 desc; # add ...
4
votes
2answers
3k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
6
votes
2answers
189 views

In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns? I am aware of the fact that PRIMARY KEY and ...
2
votes
1answer
468 views

Greater than operator ignoring a nonclustered index?

I have a pretty simple query in SQL 2008 R2 and I have a nonclustered index on the "sDate" datetime column. When I look at the execution plan for the following query it seeks the nonclustered index. ...
13
votes
1answer
5k views

When should you specify PAD_INDEX?

So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it ...
1
vote
1answer
404 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
3k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
0
votes
1answer
164 views

The sort column must be the last column used in the index - MongoDB Indexing Advice

MongoDB docs advices that: The sort column must be the last column used in the index Here is the example: db.foo.ensureIndex({a: 1, b: 1, c: 1}) Good: find(a=1).sort(a) find(a=1).sort(b) ...
2
votes
2answers
136 views

Why isn't the following query using the `always` index?

This morning I started receiving load warnings on one of the MySQL servers I manage. SHOW FULL PROCESSLIST revealed that the culprit was the following (poorly written) query: SELECT * FROM movies, ...
1
vote
1answer
3k views

Repair with keycache in mysql runs EVERY time. Can't even log why it's happening

I have a MyISAM table w/ ~1million rows and two indexes - one's a three-column key, and the other is a spatial index for bounded SELECT statements on a separate point column. My insert statements ...
1
vote
1answer
227 views

What is the performance difference between local text index and domain text index in oracle?

What is the performance difference between local text index and domain text index in oracle? Also what is the cost of rebuilding the domain text index when I have to split just one partition? Does the ...
0
votes
1answer
1k views

slow index scan on postgresql 9.1

I have a view which is amazingly slow :) It joins two tables only, having a few simple WHERE conditions and one in a form of a subquery (see SubPlan1 below). The simplest condition is checked as an ...
4
votes
1answer
10k views

Unique index corrupted SQL. Select query returns single row but create unique index fails

I am not able to repair my database due to below errors, when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) I am getting the errors listed here. When I run a select query for these key values, ...
12
votes
2answers
516 views

Are there any benefits in certain ordering of columns when defining indexes

For example, if I have two indexes: CREATE INDEX IDX_1 ON MY_TABLE_1 (ITEM, DATE, LOCATION) COMPUTE STATISTICS; CREATE INDEX IDX_2 ON MY_TABLE_1 (DATE, LOCATION, ITEM) COMPUTE STATISTICS; ...
6
votes
1answer
7k views

How to make a union view execute more efficiently?

I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process ...
3
votes
1answer
2k views

How can I set the index fill factor back to server default?

I have some indexes that I was playing with on my dev server. I set the fill factor of these indexes using the following command: alter index all on dbo.Table rebuild with (fillfactor=80) That ...
2
votes
1answer
209 views

Query results in a strange explain output

So I have a medium sized query that hits 15 tables. The query takes 36 sec on MySQL to complete. I ran EXPLAIN on the query and one of the tables returned this. '1', 'SIMPLE', 'mount_adapters', ...
3
votes
1answer
783 views

Error while creating index in SQL Azure

I'm trying to create an index on table [Children] in SQL Azure but get the following error: The operation failed because an index or statistics with name 'IX_Children_ParentID' already exists on ...
9
votes
1answer
840 views

Why does the fill factor in SQL Server default to 0 (100%)?

I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95? Is ...
2
votes
1answer
655 views

Maintenance Plan Takes Too Long - Locks Tables - Indexing To Blame

System: I have a Maintenance Plan that Rebuilds Indexes for Several Tables (10 tables, 50M records total). Issue: During the Index Rebuild (~20 minutes), we fail to insert data into the DB ...
5
votes
2answers
156 views

Are existing entries immediately included into a newly created index?

Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index: CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ...
3
votes
1answer
221 views

Is UNIQUE(), INDEX() different from UNIQUE INDEX()?

Is there any different how mysql treats UNIQUE (column1, column2), INDEX (column1) and UNIQUE INDEX (column1, column2) and UNIQUE (column1, column2) In the latter, I think mysql will ...
3
votes
2answers
223 views

What is the signifigance of a fillfactor of 20?

I have a database for our ticket system in SQL Server express. I was poking around and noticed that a lot of the indexes have a fillfactor set to 20. What is the signifigance of it being set to 20? ...
11
votes
1answer
918 views

Behavior of data in indexes based on fill factor

Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%? From my understanding, when the data is inserted there ...
6
votes
2answers
2k views

Why does MySQL choose this execution plan?

I have two queries, select some_other_column from `table` order by primary_index_column asc limit 4000000, 10; and select some_other_column from `table` order by secondary_index_column asc ...
5
votes
2answers
4k views

Postgres full text search with multiple columns, why concat in index and not at runtime?

I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns. The postgres docs talk about creating a ts_vector ...
2
votes
1answer
434 views

select for update gives error on indexed column

here is my code: mysql_query("start transaction"); $q="select max(id) from test for update"; $r=mysql_query($q); $row=mysql_fetch_array($r); for ($k=0;$k<100000;$k++) { $q="insert into test values ...
6
votes
1answer
2k views

Reindex Task Failing Randomly [closed]

We use the built-in maintenance plan to reindex (and backup, etc.) our production SQL Server. The problem we are currently having is the reindex task started failing randomly this week after months ...
4
votes
1answer
802 views

Why does it take a long time to drop a function based index in Oracle?

I have a function based index that takes 25 minutes to create on a table of 94 Million rows. When I drop the index, it takes 18 minutes. Why does it take so long? I would have thought the drop would ...
4
votes
2answers
4k views

Reindexing a large database

I have a very large database (millions of rows) which has been indexed. I receive a diff of rows from another source that I need to update in my database. The problem I am facing is that is it ...
4
votes
2answers
743 views

Computed column with less size than the “referring column”

I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column ...
6
votes
1answer
963 views

Are there any performance benefits to using a hash table with no clustered index?

I have this table: CREATE TABLE [dbo].[relatea] ( [mid] [varchar](16) NOT NULL, [sid] [varchar](16) NOT NULL ) It stores hash matches. Is there any benefit to having this as a heap? It has ...
5
votes
2answers
3k views

Dropping and recreating indexes for performance reasons?

I'm maintaining Somebody Else's Code. There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive. But it's ...
5
votes
2answers
617 views

Optimizing IP Range Search?

I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query. In my table I have 3 columns CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, ...
3
votes
1answer
1k views

MySQL ignoring index, index cardinality is the same as number of records in the table (?!)

Everyone, I could use an extra set of eyes on an issue we've come across. Here's what we're seeing: We're writing some code to migrate our messages to a new table called communications. We're ...
2
votes
1answer
2k views

Best way to compare strings using pre-built ngrams?

I'm struggling with an n-gram problem and have been hunting the Web for examples to help me out and have noticed n-grams being discussed on this forum. I'm working on a system where we've got scraped ...
21
votes
1answer
39k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
4
votes
1answer
473 views

index not being used

I have an exact same myisam table on master and slave. The indexes are not being used on Master server. mysql> explain select count(date_time) from ox_data_summary_ad_hourly where date_time = ...