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)

8
votes
2answers
241 views

Minimizing Indexed Reads with Complex Criteria

I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such: CREATE TABLE TICKETS ( TICKET_ID id PRIMARY KEY, JOB_ID id, ACTION_ID id, STATUS str256 ...
0
votes
0answers
20 views

PostgreSQL indexing table on multiple columns

I have a table that holds half hour metering values in a compact form on a daily basis. The table has a structure as described below: day: timestamp without time zone, // describes a day ...
2
votes
1answer
3k 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 ...
0
votes
1answer
26 views

Why Does the Update Statement also updates indexes?

I am currently learning about SQL queries and how indexes work with them, and I ran into a situation that I don't understand. Let's say I have this query for that updates a tables' values with another ...
1
vote
1answer
68 views

Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search?

One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words ...
3
votes
3answers
270 views

use an Index correctly to optimize queries

I have an SQL database with millions of records and when I'm querying the data like select * from ActCosts where ScenarioID= 456 The tables has 1,323,718 rows and it gives me like 50,000+ rows ...
4
votes
2answers
92 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 ...
8
votes
2answers
1k views

How to prevent daily index fragmentation of 99%

I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. ...
3
votes
2answers
62 views

New Index on table cause table to be locked really often

Here are my tables : CREATE TABLE [dbo].[Trackers] ( [IdTracker] INT IDENTITY (1, 1) NOT NULL, [IMEI] NVARCHAR (16) NULL, [CreationDate] ...
0
votes
0answers
13 views

Optimal Table Index for hourly data

I have a table that contains ~40,000,000 records and consisting of the following fields: idName - foreign key relationship to another table Date Hour Type (two possible types) Value Currently I ...
0
votes
1answer
157 views

MySQL is using wrong index

This is my table: - user_id INT (foreign key on `user` with the reference `id`) - question_id INT (foreign key on `question` with the reference `id`) - option_id INT (foreign key on `option` with the ...
3
votes
1answer
51 views

PostgreSQL 9.4 analysis, performance of normal column, indexed column and jsonb key

If I have a table containing: CREATE TABLE test( id SERIAL PRIMARY KEY, name VARCHAR(200), age INT, data JSONB ); and data column populated with {"name": xxx, "age": yyy}, sometimes ...
1
vote
1answer
117 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 - ...
1
vote
1answer
61 views

Indexed View - how to verify if it is worthy?

I have the following indexed view: CREATE view VProcessStatus WITH SCHEMABINDING AS select processstatus, StatusCount = COUNT_BIG(*) FROM DBO.tbltaxtransaction GROUP BY ...
0
votes
2answers
172 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
3answers
358 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; ...
1
vote
1answer
655 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? ...
0
votes
1answer
31 views

Slow Postgres updates if OTHER columns are indexed?

Certain updates take far too long on large Postgres tables. Given these conditions: only one column is being updated, and it is non-indexed the column already has data in it for every row due to a ...
2
votes
1answer
38 views

Dropped an index and recreated it in PostgreSQL 9.2, not used anymore

I have a table with 15M rows, with two indexes; one of them is a very simple BTREE on a string column. While trying to optimize some queries, I created a new partial index on the same column. Using ...
0
votes
1answer
76 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
32 views

Unique Constraint exception after dropping constraint

We had dropped a unique constraint using the ALTER TABLE DROP CONSTRAINT script, but it is still throwing the Unique Constraint exception while trying to insert. We drilled this down to two possible ...
0
votes
1answer
177 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
1answer
641 views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
2
votes
1answer
422 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 ...
1
vote
1answer
66 views

Index optimization on a view using a full outer join

Table structure: Foo FooChild Bar --- -------- --- ID ID ID Date FooID Date ...
1
vote
1answer
54 views

Full outer joins, coalescing, indexes and views

I have two tables, let's call them TableA and TableB. TableA TableB ------ ------ ID ID Date Date Foo Bar Both tables ...
3
votes
2answers
124 views

Using sp_BlitzIndex - Diagnosed as “Index Hoarder: Addicted to nulls”

Context: MS SQL Server 2012 In using Kendra Little's awesome tool, sp_BlitzIndex, a number of tables were diagnosed as "Index Hoarder: Addicted to nulls". I'm not looking for a debate over NULLs and ...
1
vote
1answer
40 views

Which Indexes in which situations is more efficient in SQL Server 2012

I use SQL Server 2012. I want know when I want to know in what situation I have to use CREATE NONCLUSTERED COLUMNSTORE INDEX .... and in what situation I have to use Create NonClustered Index... ...
2
votes
2answers
61 views

space usage on sys.allocation_units and sp_spaceused

It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't. I am working on a ...
-1
votes
0answers
28 views

How index work in case of write operation?

We have two cases:- Insertion :- Cluster Index:-- Insertion :- we have number of user which we doing insertion new records. For .........example 27 users had inserted 27 rows in 1 ...
0
votes
1answer
12 views

Disable TTL thread in MongoDB

Is there any way to disable TTL deletion thread in mongoDB? I know that you can modify the ttl index expireAfterSeconds value to a high number in order to "technically" stop ttl from deleting, but i ...
0
votes
1answer
86 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
66 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 ...
1
vote
1answer
77 views

Rebuild index in table with many values

I'm junior DBA ,have table with 11 million values ,one of the indexes is like : CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl] ( [my_id] DESC )WITH (PAD_INDEX = OFF, ...
0
votes
0answers
21 views

Mongodb compound index on two fields where one field is sparse

I have a collection of documents that give stats relating to videos. I have a unique compound index on videoId, date (there is at most one document per video per day). I want to be able to; in a ...
3
votes
2answers
72 views

Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing. There is an index on search_id column in search_criteria table and on pricing_id column in pricing table. But running this nested query does not use ...
3
votes
2answers
90 views

SQL Server 2008R2 XML Column Index strategy for large tables?

I am working with SQL Server 2008 R2 and some XML columns in a very large table. I understand that I cannot use Selective XML Indexes due to our SQL Server version being < 2012. I'm brand spanking ...
1
vote
1answer
220 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 ...
0
votes
1answer
73 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 ...
2
votes
2answers
129 views

SQL Server doesn't use expected index

I have the following query (simplified from an Entity Framework query, I don't have direct control of the SQL): SELECT TOP 15 HouseHoldingRuns.Id, ( SELECT TOP 1 ManCoId FROM ( SELECT TOP ...
1
vote
1answer
55 views

Check if an index exists by comparing columns

We are currently using ALL_IND_COLUMNS to check if an index exists by its name or the columns it is using. The problem is that we can only check if the index exists with columns in a specific order. ...
3
votes
1answer
54 views

Help with indexing narrow table with many millions of rows

I have a good amount of experience with SQL Server, but not much with indexes and I fear I'm a little bit out of my depth in trying to figure out how to correctly structure a table that has 100+ ...
3
votes
1answer
259 views

Adding new key(s) to enable partitioning, INSERT performance?

I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 different devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
0
votes
1answer
26 views

optimize my query mysql

I have the following query: SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId WHERE d.castName ...
1
vote
0answers
32 views

How do I recover from MySQL InnoDB table/index corruption?

While trying to upgrade a database (in place) I got the following error. The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible. ...
1
vote
2answers
1k views

Adding index to large mysql tables

I have a table | base_schedule_line_items | CREATE TABLE base_schedule_line_items ( id int(10) unsigned NOT NULL AUTO_INCREMENT, installment int(10) unsigned NOT NULL, on_date date ...
7
votes
3answers
741 views

Should I separate frequently updated columns?

I have a users table that contains users' information and a column named credits that is frequently updated. On index page I'm showing list of users with basic user information but I don't need ...
3
votes
1answer
61 views

PostgreSQL CREATE INDEX memory requirement

I've got 6GB of RAM on my laptop and for a test I'm creating a 50mil-rows table in PostgreSQL 9.3. I then want to create an index on the table. The table and the resulting index together (or twice ...
2
votes
1answer
25 views

How to get the spatial index in CREATE TO script?

Is it possible to get the spatial index using the create to script using SQL Server 2012? When I write out a table using SCRIPT TABLE AS > CREATE TO in Microsoft SQL Server Management Studio 2012, I ...
0
votes
1answer
77 views

Increase performance WITHOUT adding indexes [closed]

I'm having a rather large table (350MB, 1.3M rows) and there are dozens of queries arriving at this table every second. Most queries are super-fast thanks to indexes - but some others aren't. ...