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
vote
1answer
3k 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
140 views

Big jump in search time for Postgres Index query for results with high selectivity

I am doing some performance comparison of databases and lucene for full-text searching. So I use Postgres to create an Index for the data to search: CREATE INDEX bodies_index ON bodies USING gin ...
1
vote
1answer
2k views

Why PostreSQL 9.2 does not use index scan if it can?

My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan. Here is my table. The index I am talking about is name_left_prefix cwu=# \d web_city; ...
1
vote
1answer
396 views

SQL Index order and performance based on cardinality and data

I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting ...
1
vote
4answers
569 views

Mysql - How to Optimize Retrieval Time in a Table

I have query 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; ...
1
vote
1answer
325 views

I would like to query a range of criteria on multiple columns in MySQL

I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example : We have 3 columns as ...
1
vote
1answer
1k views

Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts ...
1
vote
1answer
423 views

Why do MySQL MyISAM table indexes go out of date?

I have few MyISAM tables, where only the auto_increment primary key is up to date with the number of columns, but not other indexes. Any idea, why this happens?
1
vote
1answer
196 views

Consolidating indexes

I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
1
vote
1answer
72 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
387 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
465 views

Mongodb multikey index and $all in queries

I have such structure of documents in my products collection: { title: 'Samsung Galaxy S III', category_id: ObjectId("50bcc2f0b910a6c1936a4424"), properties: [ { title: 'OS', ...
1
vote
1answer
1k views

InnoDB, What would cause: “db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL”

MySQL 5.5.24 . We have InnoDB tables that grew almost by double (from 50GB to 100GB - file-per-table enabled) in a few weeks due to a project we are working on. The tables were freshly dumped and ...
1
vote
1answer
138 views

Simple Slow Query Not Using Indexes

I have a slow query running on a large forum. The EXPLAIN synopsis: mysql> EXPLAIN SELECT COUNT(post_thanks.id) as thanked, post_thanks.postid, post.title, post.dateline FROM ...
1
vote
1answer
989 views

Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255). The value exists in this columns are ...
1
vote
1answer
1k views

How to interpret this “db.explain()” for this mongodb query

db.tablebusiness.find({ "LongitudeLatitude" : { "$within" : { "$centerSphere" : [[106.772835, -6.186753], 0.0089967464101566] } }, "indexContents" : { "$all" : ["warung"] }, "Prominent" : { "$gte" : ...
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
241 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 ...
1
vote
1answer
2k views

At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?

I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run. I guess that it ...
1
vote
1answer
942 views

PostgreSQL 8.4 hangs on create table, reindex

I have a rather small database(200Mb) which was running on ubuntu 10.10/amazon ec2 fine for a year almost but today I ran into the following problem: When I create a table that references other table ...
1
vote
1answer
303 views

Design Tagging module in the database

I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
1
vote
1answer
33 views

Most efficient way to retrieve data by timestamps

I'm using PostgreSQL 9.2.8. I have table like: CREATE TABLE foo ( foo_date timestamp without time zone NOT NULL, -- other columns, constraints ) This table contains about 4.000.000 rows. ...
1
vote
0answers
29 views

Index on a highly updated field causing excessive locks in SQL server

I'm in a bit of a conundrum about what the best course of action on this (bad?) index is. I have a table which i use as a notification holder that i watch for database changes with. CREATE TABLE ...
1
vote
0answers
27 views

What is the maximum value for the index_length of a MySQL 5.5 table?

I'm trying to find out why a series of inserts is painfully slow (1 minute 15 seconds, on average) on a dedicated MySQL 5.5.47 server, while the same inserts are normally fast (less than 5 seconds) on ...
1
vote
0answers
25 views

Mongo indexes question

I have the following aggregation query: db.getCollection('usersongs').aggregate([ {$match: { room: 'someObjId', status: { $in: [ "ended", "skipped" ] } } }, {$group: { ...
1
vote
0answers
49 views

sys.dm_db_index_physical_stats slow on first run after rebuilding indexes

I have a 10GB database in SQL Server 2016 RC1 on Windows Server 2012 R2 where I just performed many insert and delete operations. Afterwards, I ran: EXEC sp_MSforeachtable @command1="SET ...
1
vote
1answer
62 views

SQL Server : ETL Stage Index

We have a very large staging table (> 80 GB). From our source system we load invoice data in the staging table. From the staging we transform the data and load into DWH/Fact. Every day we delete the ...
1
vote
1answer
23 views

Postgres odd behavior with indices

I've got a datavalue table with ~200M rows or so, with indices on both site_id and paramter_id. I need to execute queries like "return all sites with data" and "return all parameters with data". The ...
1
vote
1answer
30 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
1
vote
0answers
52 views

Cardinality of an index in a MySQL table is equal to table's row size

I have just included an index to a MyISAM table and the cardinality appears to be 1 million something which is exactly the number of rows in the table. In fact it needed to be 315 because distinct ...
1
vote
0answers
53 views

Query with join to a table on a different database need an index

I'm creating a query that one of its joins is made to a table from a different database. The query analyzer is suggesting the creation of an index, however I don't have permission to add objects to ...
1
vote
0answers
42 views

Mysql slow copying to tmp table to indexing

I try to indexing table. The table size is about 60GB. But indexing query is hang on 'copying to tmp table' 18 hours. I found advice setting 'myisam_sort_buffer_size' myisam_sort_buffer_size : 128M ...
1
vote
0answers
53 views

PostgreSQL doesn't use index on inherited tables

I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS: an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
1
vote
1answer
72 views

Would adding an INDEX on varchar column in Mysql increase performance?

I have the following table to store log messages: CREATE TABLE `log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `timestamp` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL, `logger` varchar(255) ...
1
vote
0answers
23 views

How to bucketize using Query optimal bucketization algorithm

I am trying to implement query optimal bucketization algorithm (QOB). The input of the algorithm is D=(V,F) where V is the value of the attribute and F is the frequency of occurrence. For EXAMPLE D ...
1
vote
0answers
26 views

Oracle unable to extend index but I have many space free

I have application using Oracle database, and when I'm trying to call my pl/sql function which inserts many rows in tables I get an error: unable to extend index MY_INDEX by 128 in tablespace ...
1
vote
0answers
56 views

Which indexes should I create?

I need assistance in which indexes to create on a table, and the best approach for querying it. We use PostgreSQL. Here’s the table: create table myt ( the_time timestamp, oid int, ...
1
vote
0answers
60 views

SQL Server index rebuild cause database growth

I have a 80GB database with about 20GB of indices size. Whenever I build the indices online the database file will grow to gain 20GB of unallocated space, and if the unallocated space is less than ...
1
vote
0answers
25 views

Index comparison

On my Production server (SQL2008) I have a query that returns suggestions of indexes. SELECT * FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats gs on ...
1
vote
1answer
128 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
1
vote
0answers
114 views

Does “CREATE INDEX WITH DROP_EXISTING” use the bufferpool for the recreated index?

We have recreate our 4TB clustered index in ouor SQL Server. Even thought we have 1.5TB of memory we still run into the following error message: "Msg 802, Level 17, State 2, Line 30 There is ...
1
vote
0answers
39 views

How does PostgreSQL compute the cost for index scan?

Consider the following plan: Index Scan using foo_c1_idx1 on foo (cost=0.00..8.38 rows=1 width=37) (actual time=0.012..0.013 rows=1 loops=1) Index Cond: (c1 = 500) Buffers: shared hit=4 Total ...
1
vote
0answers
44 views

Can an index become inconsistent for “no reason”?

Of course, when i say no reason i mean "apparently" no reason because nothing happened on the server (forced shutdowns, data corruption, deadlocks, etc...). I ask this because, there was an ...
1
vote
1answer
147 views

Using index on datetime field not working really

Here is my query select msisdn, sum(score) as score from contest_player_update_history_ideacontest6_35 where request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' ...
1
vote
0answers
60 views

Fragmented clustered primary key (sequential GUID) index after processing - SQL Server

I'm in the process of evaluating a schema change on the primary keys of several of our biggest tables in our software. Till now we used varchar(36) for all of our PKs and FKs and Hibernate generated ...
1
vote
0answers
37 views

DMV Index proposals still reported as missing

We have recently converted a big client running our patient/journal application from ORACLE to MSSQL, and after the conversion we have had quite a few performance issues. After running the DMV query: ...
1
vote
0answers
14 views

Query INFORMATION_SCHEMA for list of indexes [duplicate]

How does one query INFORMATION_SCHEMA in a standards-compliant database such as Postgres to obtain a list of all indexes in one database/catalog?
1
vote
0answers
294 views

Script to find unused indexes over a period of time doesn't work in Oracle 11g

The statspack_unused_indexes.sql script described in this page would be incredibly useful to me, but it has been coded for an earlier version of Oracle. When I try to run it on 11g, I get the ...
1
vote
0answers
17 views

Access algorithms in Partial Index implementation of PostgreSQL 9.4?

I am studying big-O time-space complexities of different algorithms with the partial index of PostgreSQL 9.4: introsort (O(n log n)-time, O(log n)-space) and heapsort (O(n log(n))-time ...
1
vote
0answers
45 views

Speeding Up Query - Hangs at Sending Data - Indexed Correctly

I am looking for ways to speed up the below query. I currently have indices on FIRST, LAST, UNIT_PARENT, HomeAddressLine1, HomeAddressLine2, HomeCityNAme, HomeStateAbbreviation, HomeZipCode, HomeZip4, ...