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
0answers
38 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
69 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 gs....
1
vote
0answers
133 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
58 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
45 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
0answers
73 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
38 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
411 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
18 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 O(1)-space),...
1
vote
0answers
46 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, ...
1
vote
0answers
69 views

Database fragmentation and heap SQL Server

We have critical performance issues with our production database (its around 300 gb size) We have run the following script for a few huge tables : DECLARE @db_id SMALLINT; DECLARE @object_id INT; ...
1
vote
0answers
62 views

Mariadb support rtree index on innodb soon?

I was looking through mariadb bug list online (https://mariadb.atlassian.net/secure/RapidBoard.jspa?rapidView=24&projectKey=MDEV), but I am unfamiliar with JIRA so I am not sure exactly what I am ...
1
vote
0answers
23 views

Calculate Num_Index_Pages for sizing design

I don't understand Step2 #8 Calculate the number of number of non-leaf pages in the index. I am not sure I understand the "Level". I have Num_Leaf_Pages=508 & Index_Rows_Per_Page=476 I don't ...
1
vote
0answers
154 views

Query Performance - Why is a query slower when there are two criteria on the same column?

I have these two queries against a view, where I've added WHERE clauses against a date column (Indexed, Unique, Non-Clustered, Ascending): SELECT ColA FROM MyView a WHERE a.[Date] >= DATEADD(...
1
vote
0answers
64 views

Multiple SQL Server Indexes, which ones to keep?

I am looking at my database that several people have worked on. A particular table is responding slowly when queried on a certain join. I notice some of the parameters of the join are not indexed. ...
1
vote
0answers
464 views

Mongo range query became extremely slow on large collection

I have a collection which has 50 million records in it. After using wired Tiger its size became only 3 GB (2 GB data 700 MB index). I have created index on this collection. But when i do a range query ...
1
vote
0answers
69 views

Defragmentation of SQL Server 2008 indexes

I am having issues defragmenting indexes. I keep using the various defragmentation methods for defragmenting a SQL Server index such as DBCC INDEXDEFRAG and DBCC DBReIndex, but none seem to be ...
1
vote
0answers
112 views

MySQL table partition strange behavior (slow query suddenly)

(I asked this question in "stack overflow" and was recommended to ask in here). (MySQL version: 5.6.15) I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a ...
1
vote
0answers
114 views

MySQL not using multicolumn index on self join

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows. /* Count all occurrences between start and end site id of a given speed ...
1
vote
0answers
252 views

MySql: Slow join and group by on indexed columns

The following query takes 15-20s to run SELECT "Trade_US"."ID" AS "Trade_US.ID", "Server_US"."Name" AS "Server_US.Name", "Trade_US"."TradeTypeID" AS "Trade_US.TradeTypeID", "Users"."ID" AS "Users....
1
vote
0answers
81 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
1
vote
0answers
189 views

Best practice for storing image order number in a database

I am designing a social networking website(mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:- PicId (BIGINT, AUTO INCREMENT, PK) ...
1
vote
0answers
113 views

Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table

I have a very large table (15 billion rows) with a structure like this: CREATE TABLE `textlines` ( `PGUID` binary(16) DEFAULT NULL, `StatID` int(11) DEFAULT NULL, `Time` datetime(3) DEFAULT ...
1
vote
0answers
27 views

Why do these two queries (which I think are equivalent) rely on different keys?

We have a query that we don't like, since it ends up doing a full table scan. EXPLAIN SELECT count(*) AS c, location.name FROM study LEFT JOIN location ON study.location_id = location.id ...
1
vote
0answers
407 views

What is a loose index scan?

I was reading that MySQL can not use an index on 2 range conditions due to not having a "loose index scan" What exactly does this mean? Is it related to the underlying or the implementation of the ...
1
vote
0answers
49 views

Database tuning and possibly unexpected results

(This is cross-posted from stackoverflow; I initially put it in the wrong place.) So I have a database that's working a bit slower than I would expect and I'd like to describe what I'm doing so ...
1
vote
0answers
121 views

How to take advantage of multiple spatial indices?

All, I'm building a view which uses a spatial intersect (STIntersect in MSSQL 2012) between points (student addresses with address information) and polygons (school boundary polygons with school ...
1
vote
0answers
887 views

Based on these DBCC SHOWCONTIG stats, should I index this table?

I have a SQL 2008 Standard server on full mirror (safety). I reorganize all indexes weekly. I am looking into rebuilding a few indexes to improve performance. I ran DBCC SHOWCONTIG on my largest, most ...
1
vote
0answers
56 views

What's Scalar Operator in the plan?

I have a doubt. What means this Scalar Operator in the print below? I've been investigating our database cause we have severals RESOURCE_SEMAPHORE_QUERY_COMPILE . I've seeing a lot of queries using ...
1
vote
0answers
164 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
1
vote
0answers
701 views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
1
vote
0answers
6k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
1
vote
0answers
47 views

table scheme definiation it this way that insert duplicate into another table

I have a table in which there is a column name with SP varchar(10) NOT NULL. I want that column always to be unique so i created unique index on that column . My table schema as follows : CREATE ...
1
vote
0answers
336 views

Fulltext search returning too many rows

Server: MySQL 5.1.66-0+squeeze1 My problem: I have two databases with the same tables. One is for the testing environment and one for production. When I perform a MATCH AGAINST on the prodution ...
1
vote
0answers
83 views

Oracle - Mysterious indexes

I have a table in Oracle 10.2g that I'm trying to import roughly 11 million rows into. The table contains an SDO_GEOMETRY column. It previously had NOT NULL constraints on all 3 columns, a primary key ...
1
vote
0answers
45 views

parenthetic grouping of join clauses

What is the difference between these joins? a left join b left join c (a left join b) left join c a left join (b left join c) Does the grouping only affect the order of the joins? Will the query ...
1
vote
0answers
39 views

How does MySQL 5.1 use indexes?

I've read the docs for MySQL v5.1.x on how about the indexes are used, but I'm not sure I understood it properly. Suppose having the following MySQL table: CREATE TABLE IF NOT EXISTS `...
0
votes
0answers
19 views

Auto increment id column vs index VARCHAR columns

I'm wondering the differences or comparisons of having an Auto Increment int Id column or an indexed VARCHAR(32) column. Which one would be faster if I had a second table that references the first ...
0
votes
0answers
31 views

Does having a .ldf for a index file cause issues?

Interesting find today, I have a file group that is used for indexing. It has one file that has a .ldf extension, which as you know for SQL Server, is the extension for the transaction log files. My ...
0
votes
0answers
30 views

SQL Server Index recomended but already there (DMVs)

I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc. Some examples are Index ON X ( Y ) and Table ...
0
votes
0answers
24 views

pt-duplicate-key-checker - DBD::mysql::db selectall_arrayref failed: MySQL server has gone away

I run pt-duplicate-key-checker a few months ago successfully. Now I receive on the same database (Galera, RHEL7) those errors DBD::mysql::db do failed: MySQL server has gone away On CLI I can do use ...
0
votes
0answers
60 views

Suggestions for indexes or how to partition a table in SQL Server 2008R2

Table structure and DBMS SQL Server 2008 R2 I have a single table structure (flat) that has summarized sales data (YTD,MAT etc) by unique combinations of "Customer", "Product" and "Territory". ...
0
votes
0answers
59 views

When to reindex postgres index?

I want to know if I need to reindex a PostgreSql index, ith SQL Server, I check for index fragmentation and then decides whether a Rebuild, a Reorganize or no action is needed. When should I reindex ...
0
votes
0answers
79 views

How could I improve speed of fetch in Oracle?

I've got a table of taxi GPS trajectory records with approx 60 million rows, including date, time, ID, longitude, latitude, speed, bearing etc., which were imported from .csv files with setting each ...
0
votes
0answers
56 views

Why my MySQL (myisam) index is broken and how to avoid it?

I have big composite (multiple-column) index on MySQL MyISAM table. And I noticed it doesn't work. I ran SHOW INDEX command. There are NULL in the Cardinality column on the most index fields (except ...
0
votes
0answers
29 views

N-dimensional vectors in PostgreSQL Spatial Index

I have many 8-dimensional vectors. And I want to construct on them a R-tree ('USING gist' in PostgreSQL). But I have got stucked at the choice of the correct geometrical type. Whether the 'path' type ...
0
votes
0answers
55 views

Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished? I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until ...
0
votes
0answers
35 views

Weird single-column “key” index in mysql

I am running MySQL 5.5.47-0+deb7u1-log (Debian). In a database I have two tables with exactly the same structure, roughly the same data (don't ask me why, its a long story..., and I know they are ...
0
votes
0answers
44 views

Is there an equivalent of virtual columns in mysql?

Currently I'm taking a look at amazon's new aurora database and I'd like to check how my current setup performs there. The original table is on maria DB and contains a virtual column `...