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)

-1
votes
4answers
68 views

Index on 'GROUP BY' with functions

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
1
vote
1answer
567 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
2
votes
1answer
30 views

Why is my composite index not used for this table?

I am working with a fairly complicated schema, but I've simplified it for the purposes of this question. Table "public.scheduler_appointment" Column | Type ...
1
vote
0answers
33 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
3
votes
1answer
44 views

difference between index REBUILD and REORGANIZE in relation to recompile in SQL Server 2008 R2

Does the INDEX REORGANIZE index cause recompilation of the stored procs the same way as INDEX REBUILD
1
vote
1answer
30 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not ...
2
votes
0answers
37 views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
5
votes
1answer
994 views

How can I get status messages during a large re-indexing operation with MySQL 5.5?

I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and ...
6
votes
2answers
1k views

Bitmask Flags with Lookup Tables Clarification

I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
1
vote
1answer
80 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
1
vote
0answers
26 views

MongoDB index strategy

I'm developing a system to back-up computers and store the files / data with retention for a while. The actual files are stored in an object store, while I'm planning to keep the metadata of the files ...
3
votes
1answer
54 views

Very large btree index with few rows (openstreetmap gis data)

I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
0
votes
1answer
41 views

Tablespace of an index organized table

I want to find the tablespace of an index organized table with primary key. With dba_segments I can only find the tablespace for the primary key. But how can I find the tablespace for the table?
0
votes
3answers
38 views

MYSQL Indexes when looking up on alternating sets of columns

I have a table that looks like: CREATE TABLE `connections` ( `connection_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `service_id` smallint(5) unsigned DEFAULT NULL, `parent_id` bigint(20) ...
0
votes
1answer
33 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
2
votes
2answers
67 views

I need help optimizing a not that complicated query that returns unexpedcted performance problems

I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the ...
1
vote
1answer
259 views

optimizing MySQL for traffic analytics system

background : I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as ...
1
vote
1answer
99 views

Why is this second query more expensive than the first?

While reading Graph Databases book, I came across this excerpt: I can't figure out why the second query is more expensive than the first. Indeed, both filter on the Bob person. May anyone ...
0
votes
1answer
56 views

When to rebuild and when to reorganize indexes [duplicate]

I have been trying to gather information about rebuilding and reorganizing indexes. From stackoverflow page (How Often should the indexes be re-build in sql-server DB?) i got this query : SELECT ...
0
votes
1answer
40 views

When to use hashtable over binary search tree vice verse for Database Index

Database Index can be constructed using binary search trees (BST) or HashTables , From my understanding so far , HashTables are superior to BST in terms of time taken for searching and insertions ...
0
votes
0answers
27 views

Mysql table with only two columns, nothing unique: Will an index increase search performance?

If my table only has two columns (username, groupname) and a million or so rows, will an index improve performance at all? Wouldn't it take just as long to search the index file as it would to search ...
0
votes
1answer
31 views

Varchar indexing

I am very rusty with MySQL and I'd like some advice. I have several software tools that are deployed throughout the company and I am writing a DLL to keep track of the usage, errors and stuff of some ...
0
votes
1answer
62 views

Indexes and their uses [closed]

Index improve performance in select statement, but what is the difference between this create clustered index ix_index1 on table1(col1) create clustered index ix_index2 on table1(col2) and this ...
4
votes
2answers
111 views

Clustered Table Scan Because of “SELECT *”

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths: CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( ...
0
votes
1answer
17 views

Creating unique constraint to be validated from input

I have this table name tbl with this table definition: create table tbl ( id serial primary key not null, counter integer not null default 1, ref_id integer not null, date_booked date ...
-1
votes
3answers
61 views

Full table scan even after adding index

i have two tables in Oracle 11g server one is partitioned and another is having a 3GB of data. I've added a composite index on both the tables based on the where clause, in the order it appears. The ...
4
votes
2answers
2k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the queries results is ordering it according to the user's language during the ...
1
vote
1answer
134 views

How to repair all indexes in a MySQL database?

My database got corrupted, and now the php app is throwing this error: Invalid query: Can't find FULLTEXT index matching the column list The error is not very useful, since it doesn't say what ...
1
vote
1answer
37 views

best possible index on this situation

create table #testing(id int, name varchar(50)) go Insert Into #testing Values(231, 'fasd') Insert Into #testing Values(232, 'dsffd') Insert Into #testing Values(233, 'xas') Insert Into #testing ...
0
votes
0answers
71 views

How to know the correct indexes for mysql?

I tried to find out with explain where I must set the correct index. A current select takes about 20 seconds. Can someone tell me which would be the correct index for my tables? SELECT trade.*, ...
0
votes
0answers
20 views

Creating a table for indexing another table

We have a table with 350m rows and 12 columns, and around 1m rows adding everyday. We have crappy hardware and we cannot add an index on one of the 12 columns that we really really need to. (severe ...
0
votes
1answer
25 views

Optimizing a query so MySQL will use the indexes for the joins

Some context: migrating some data from a 3rd party cloud MongoDB platform service into MySQL. Wrote a query but it's very slow. Here's the query: SELECT Table1.*, (long list of fields for each ...
1
vote
1answer
69 views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
1
vote
4answers
99 views

Profling for Index Maintenance

I am wanting to perform some index maintenance, in particular we have a poor performing query against a particular table. Is it a sensible strategy to profile on RPC:Completed, SP:StmtCompleted and ...
0
votes
1answer
115 views

Should I index before or after importing a dump?

I am using mongodb to persist a very big-size (90G), which has nearly 40,000,000 items. I read and parse this file and insert all items into mongodb (my programming language is perl, batch_insert, and ...
2
votes
1answer
164 views

Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?

I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
0
votes
0answers
31 views

Rebuilding an index not working? [duplicate]

I noticed that when I right click manually on an index and choose "Rebuild" or "Reorganize", its not giving any indication that its doing so, then I click on properties again for the same index, I see ...
0
votes
1answer
44 views

Postgres table growing on massive updates

I have an issue with my postgres database, I'm running massive update queries (1000 per second) to a single table (with 3000 entries) and I can see that the size of that table is growing and growing ...
1
vote
1answer
95 views

How to find MongoDB index statistics

Is there any way we can figure out what all indexes are cached into RAM ? I want to know page related stats for individual indexes (# of hits and miss )
0
votes
1answer
25 views

Index Maintenance Plan

At our organisation we are migrating to SQL Server from visual foxpro dbf and hence had no maintenance plans and have no DBA. We have gone through our backup plans and Index maintenance is only index ...
1
vote
0answers
32 views

Clarify unique constraint and index, cannot grasp the concepts to 100% [duplicate]

When reading about UNIQUE, I understand the concept but when it comes to the practical part of it, I get confused and I'm unsure of what it means. What I do understand, is that there are two ways to ...
0
votes
2answers
136 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
0
votes
1answer
83 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
0
votes
3answers
258 views

Mysql - How to Optimize Retrieval Time in a Table

I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table... mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3; ...
0
votes
1answer
22 views

Can PostgreSQL index calculations be multithreaded?

During a single query write of approximately 300,000 rows with 30 columns all indexed, I noticed that only one core was maxed at a time. Is there any way to calculate indexes multithreaded for single ...
2
votes
2answers
74 views

Is it always faster to create indexes after loading data?

I have a large number of records (~1 billion) that I need to load into MongoDB (actually TokuMX, but whatever). I have about 6 different indices I need to create on the collection. Is it always faster ...
1
vote
3answers
243 views

What's the most efficient UUID column type

For storing a 128 bits UUID there are multiple storage options: a byte[16] column two bigint/long(64 bits) columns a CHAR(36) column - 32 hex digits + 4 dashes. a UUID database specific column, if ...
2
votes
1answer
1k views

MySQL requires FORCE INDEX on huge table and simple SELECTs

We have an application which stores articles from different sources in a MySQL table and allows users to retrieve those articles ordered by date. Articles are always filtered by source, so for client ...
2
votes
1answer
108 views

Optimal hash technique to index large text

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths. The upper limit for the insertion rate is tens of billions of rows per ...
2
votes
1answer
58 views

Character varying index overhead & length limit

I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million. I have read about B-Trees, but I can't determine how much disk ...