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)

0
votes
0answers
143 views

TEMP Tablespace Exceeded when DBMS_REDIFINITION

Oracle Environment. Table size is 500Gb. Free space left on hard drive - 300Gb. TEMP Tablespace is currently 20GB Task: do the DBMS_Redefinition for the table (partition by month) and optimize ...
1
vote
1answer
97 views

Does PGStrom calculate relational comparisons?

I prefer index calculation and comparison be executed on the GPU. I have stumbled upon PGStrom. It seems one could infer that it will calculate index comparisons, but the claim doesn't seem to be ...
0
votes
1answer
839 views

What is the cost of adding a foreign key in mysql?

I have an application which has a product_def table defining all products. The recrods in product_def are fixed and will never be deleted. CREATE TABLE `product_def` ( `product_id` INT( 5 ) ...
1
vote
0answers
237 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 ...
0
votes
0answers
23 views

get mysql index size split by index [duplicate]

I have ran this query : SELECT Table_NAME "TableName", data_length /1024 /1024 "table data_length in GBytes", index_length /1024 /1024 "table index_length in GBytes", ...
1
vote
1answer
69 views

Whats wrong with my index?

I have a table, here is the structure: CREATE TABLE `table_data_cache` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` date DEFAULT NULL, `enterTollgateNumber` bigint(20) DEFAULT NULL, ...
1
vote
1answer
111 views

BitmapIndexScan vs IndexScan tuning

I have the following test case: CREATE SCHEMA IF NOT EXISTS long; SET search_path TO long,pg_temp,pg_catalog; CREATE TABLE long WITH (autovacuum_enabled=off) AS SELECT id, (random()*1000)::int AS ...
3
votes
1answer
890 views

Using an index for both asc and desc on a string column

I know a trick to make order by queries faster when we want to order by asc and desc. For integers store a negated value. So if I store -7,-11,-8,-5,-1,-2 the order by asc will give -11, -8, -7, 5, ...
2
votes
2answers
44 views

PostgreSQL query slows due to ORDER BY even with an index

I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes ...
3
votes
1answer
124 views

Total Fragments 99% and I cant get it to stay down [closed]

I have a SQL Server 2008 R2 database it is of size 13000.31 MB and my largest table has around half a million records. It is a database that I have bought. It already has indexes but when I run the ...
1
vote
0answers
121 views

Measure fragmentation of a single index?

We are measuring the impact of different defrag approaches that we do one index at a time. How do we query to see fragmentation of a single index, by index name (or table + index). This is on SQL ...
3
votes
2answers
809 views

PostgreSQL 9.3: Index on to_char()

How can I index a to_char() of a column? I have tried: adam_db=> CREATE INDEX updates_hourly_idx ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00')); But got the error: ...
0
votes
1answer
615 views

Explain doesn't show indexed column as key

Consider the following table structure CREATE TABLE `games` ( `game_id` bigint(20) NOT NULL AUTO_INCREMENT, `players_no` tinyint(4) DEFAULT NULL, `startedon` datetime NOT NULL DEFAULT '0000-00-00 ...
1
vote
1answer
129 views

How do i increase the total MyISAM indexes?

I have just made a check for my server with mysqltuner. -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster ...
1
vote
0answers
73 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
2answers
2k views

Compare Indexes/Constraints between two SQL Server databases

I have two databases on one SQL Server that I would like to compare Index/Constraints. The two databases should be identical but I have a suspicion that one database is missing a index or constraints. ...
0
votes
1answer
591 views

Oracle Text CTXCAT Domain Index, Transactional, and Before Insert/Update Trigger

TLDR: The CTXCAT index is supposed to be transactional, but Before Insert triggers that modify the indexed column don't seem to induce an update. I'm running on Oracle 9i. (Actually, I'm running on ...
0
votes
1answer
131 views

Why isn't this index helping my InnoDB MySQL query?

I'm having trouble understanding why an index I have on one of my tables is doing nothing to help the performance of a JOIN, even when forcing the index. Here is the query: EXPLAIN SELECT * FROM ...
2
votes
1answer
173 views

Adding Indexes To a Partitioned table in SQL Server 2012

I have recently converted our one large table to use table partitioning in SQL Server 2012. It's a table that stores logs based on a time. So I created a partition function to split the table on my ...
1
vote
2answers
124 views

How to add an “INCLUDE” part in an Index Created as a part of CONSTRAINT

I am going through the execution plan of a tsql query. There is a key LookUp (Clustered) in the plan. To avoid this Key LookUp, I tried to Include a column (Status_Ind) to The Index (The Index ...
1
vote
2answers
5k views

How to resolve: Specified key was too long; max key length is 767 bytes [duplicate]

Our server is creating a lot of tables and one of them is the Lock_ table. We are using MySQL 5.5 on Percona. Also we are using utf8_general_ci and utf8. It throws the following error when creating ...
3
votes
1answer
3k views

MySqlDump's DISABLE KEYS have no effect on import

I have a follow up to my previous question regarding import speed with Inno-Tables (suprise!). Scenario I try to import some big* database dump on my local dev machine in reasonable time. We have a ...
0
votes
0answers
43 views

LIKE or CHARINDEX to check String Contains a Substring [duplicate]

To check a string contains a sub string or not we can use [LIKE] or [CHARINDEX]. I know that like uses indexing in case we have name%.(while %name% may not use index) .Also charindex does not use ...
1
vote
1answer
121 views

Is database record access sequential after retrieving the indexed page from disk? [closed]

This question was posted on Stack Overflow but left unanswered, I was hoping maybe here I might find an answer. What I understand about databases that if a queried or requested record is found in the ...
2
votes
0answers
71 views

Can there be any case where it would require additional indexes while all columns compose primary key

Ok i don't know whether this question is asked before or not Here my table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblProductPagesFeatures]( ...
1
vote
1answer
906 views

Why isn't SQL Server using my non-clustered index and doing a clustered index scan?

Here are my full tables: CREATE TABLE [dbo].[tblCrawlUrls]( [cl_IdUrl] [int] IDENTITY(1,1) NOT NULL, [cl_CrawlNormalizedUrl] [nvarchar](200) NOT NULL, [cl_RooSiteId] [smallint] NOT ...
1
vote
0answers
163 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) ...
3
votes
1answer
95 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
5
votes
2answers
364 views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
1
vote
0answers
103 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 ...
0
votes
0answers
178 views

Index intersection issue with Mongo 2.6.4

I upgraded my system with 2.6.4 binaries to use specifically index intersection. But when i tried this my DB got down. I created these indexes in my collection "a_1_b_1" : 1.2 GB, "c_-1" : 500 MB ...
3
votes
4answers
558 views

postgres: index to SELECT strings like '%foo%';

I have an expensive, periodic PostgreSQL 9.3 query of the form: SELECT * from mytable where name NOT LIKE '%foo%'; foo is actually a constant here that never changes. The query is expensive ...
1
vote
1answer
486 views

MySQL FullText search on string shorter than 3 chars returns no rows

I have a MySQL table with some addresses in it. Let's say I have the following 2 rows: "10 Fake Street" "101 Fake Street" I'm trying to use fulltext search with MATCH() AGAINST(). I have set ...
4
votes
1answer
7k views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
3
votes
2answers
560 views

Postgresql - Do Foreign Key Constraints Automatically Create Indexes?

Per the PGSQL 9.3 docs: Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on ...
4
votes
1answer
2k views

Sql Server - Select while insert on another transaction gives unexpected results

I stumbled at situation that changes my knowledge about transactions and locking fundamentally (I don't know much though), and I need help to understand it. Let's say I have a table like this: ...
4
votes
1answer
210 views

Combining full-text and scalar index

Let's say we have a database of 12 million names and addresses that need to be searchable using full-text, but each row contains an integer value too, let's say COMPANYID. The table contains around ...
2
votes
0answers
40 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
1
vote
1answer
90 views

PostgreSQL: Another seq scan where an index scan is expected

Here's the query: SELECT DISTINCT ON(points.track_id) participants.contest_number, points.track_id, points.lat, points.lon, points.alt, points.timestamp, points.h_speed, ...
0
votes
1answer
180 views

Use index intersection feature in mongo 2.6 to compound shard key

I am having three indexes like name,age,salary. I can use them as compound index as per 2.6 (name and age together). But can i use the same indexes as compound shard key like name and age. i also have ...
1
vote
1answer
142 views

Indexing the difference between two columns in MySQL

Is there a way to optimize/index a query that sorts the difference between two columns. I have a modest sized table of 5 million rows that I'm trying to do the following: SELECT pk_column, column_x, ...
3
votes
2answers
103 views

How to tell which indexes are not being used

Postgresql 9.3 Debian 7 I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they ...
4
votes
1answer
383 views

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
1
vote
1answer
37 views

Index on integer column not helping. Help? (EXPLAIN ANALYZE output inside)

The table market_trades has an integer column market_id which takes values from 1 to 5. This seems like a pretty straightforward query to get the number of trades on each market: WITH markets AS ( ...
-2
votes
2answers
70 views

Indexing into two “buckets”

I'm creating a database with PostgreSQL that has an integer field called "delay". All of my queries on this column will be checking if the value of this column is less than or equal to 0, or greater ...
3
votes
1answer
88 views

Does sp_indexoption cause index rebuilds?

I want to switch AllowRowLocks=ON on a number of indexes. Some of those indexes are on large tables, and I don't really want to rebuild or reorganize the index. It is not clear from the MSDN article ...
1
vote
0answers
26 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 ...
3
votes
1answer
610 views

Online index rebuild LOB types

I have read and heard that you cannot perform online clustered index rebuild for tables that have a varchar(max) type. This (as I have read) also should be true for nonclustered indexes that contain ...
0
votes
1answer
35 views

select where index + other field

Learning indexing I have made an index, consisting of usergroupid and birthdate I understand that queries will then benefit from indexing when searching usergroupid and/or birthdate But what ...
2
votes
1answer
205 views

Why is my filtered index being ignored?

--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i<100 BEGIN ...