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)

7
votes
1answer
123 views

Will a Full-Text Catalog with Track changes: AUTO automatically update if Change Tracking for that table is not enabled?

I have this database table that is suppose to keep the full-text index up-to-date. However I am not seeing it happening at all (no error in the log because the last log I see was when I triggered it ...
-1
votes
1answer
61 views

How to fix slow MySQL Joomla query(s)

I have a few slow mysql queries and i read that fixing it is easy by just adding indexes to the table. But as of what i am supposed to be indexing in these queries i do not know. (Or how) First query ...
1
vote
2answers
107 views

Are these database indexes correct for my search

I am new to Database Administration. I am following information provided by: http://www.brentozar.com/blitzindex/ I have a table VehicleMonitoringLog. When I execute the sp_BlitzIndex script it shows ...
0
votes
1answer
37 views

Why is MySQL 5.5 ignoring this index?

I have a pretty simple query that is not using an index I expect it to because of the WHERE. Table: CREATE TABLE `cba_device_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `device_id` varchar(30) ...
0
votes
1answer
47 views

Indexing a date field on MongoDB - best practice

Hi We would like to index a date field on a potentially large collection. Is indexing a date-time field a good practice? Or should we stick to a date field (e.g. 07/12/2017) indexing (both of the ...
1
vote
1answer
43 views

What options are there for rebuilds of indexes on large tables for a data warehouse?

I have a bulk loading process that loads millions of records into a few fact tables within a warehouse. Those tables are primarily clustered on time. The non-clustered indexes are in place for how the ...
3
votes
1answer
68 views

calculating the total number of rows in a table - fails when it is a heap - is there any fix?

I have a procedure that I use to find out how big is a table. select * from sys.objects where name = 'WMS_SLALoad' -- to get the object_id of my table -- in this case it is 2041058307 SELECT ...
2
votes
1answer
19 views

Why does PSQL not allow hash indexes with Bitmapscan?

For doing a simple equality query such as EXPLAIN SELECT * FROM table where x = 1; I see that a Bitmap Index scan is done using the BTree indexes. Apparently, Bitmap Index scans disable Hash ...
2
votes
0answers
67 views

Rebuild index online without risk to other requests

We have a MS sql server 2012 enterprise database whose indices fragment very fast. We use a maintenance plan that reorganizes those indices daily, and rebuilds them weekly. The rebuild happens ...
0
votes
1answer
18 views

How does the correlation statistic parameter affect the cost of index scan?

I have the table customers having customerid SERIAL as a primary key. Initially I had the following statistic for that column: attname n_distinct correlation customerid -1 ...
2
votes
2answers
66 views

Performance issue during count Postresql 9.4 - Creating index on pivot table key…?

Disclaimer: I am new to Postgres, I need some advice... I have a huge table, with full of poker hands.(will contain 20M records, right now 2M records in it) aggregated_hands I have a pivot table, ...
0
votes
1answer
78 views

Postgresql: Optimize finding all distinct JSON keys from a colulmn

I'm looking for a way to index / optimize finding only the keys in a JSONB column. The use case is an ETL operation where I need to take data stored in the JSONB column and output it as delimited ...
0
votes
1answer
91 views

How to optimize MySQL SUM query even after adding index

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table ...
2
votes
2answers
51 views

Index-refresh in SQL Server

Is there any way to identify when a specific index was last rebuilt; unless it is used as a batch maintenance activity? In SQL Server 2008R2 or in advanced versions.
3
votes
1answer
76 views

mysql not using index on production query, yet does on staging and local

I have this query: select m.id meal_id, SUM(FORMAT( ((((fi.calories / fi.weight_in_grams) * w.GRAMS) * i.amount) / r.serves) * mr.servings , 3)) calories from meals m join ...
2
votes
2answers
469 views

Why SQL Server has 900 byte index size limit

Why can't we create an index having size greater than 900 bytes? This question was raised in a presentation to which no one had an answer. I can think of this way to answer this question. Page size ...
1
vote
1answer
76 views

Discover missing foreign keys and/or indexes

We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes. I'd ...
4
votes
5answers
283 views

Does index rebuild time depend on the fragmentation level?

Is the required time for index rebuild dependent on the level of fragmentation? Does the rebuild of a 80% fragmented index approximately take 2 minutes if the rebuild of the same index fragmented ...
11
votes
1answer
182 views

Dropping unused indexes - Assessing the unexpected dangers

We have a very large database with hundreds of unused indexes according to DMV statistics, which have been accumulating since the server was last rebooted in July. One of our DBAs made the following ...
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, ...
0
votes
1answer
47 views

Is there a way to optimize this using indexes?

I'm just now learning indexes, and I was wondering if it's possible to optimize this code using them. Currently this code runs in 1.4 seconds. SELECT C.yearID as year, name as teamName, ...
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 ...
0
votes
0answers
39 views

adding new column slows down select statement

I have a table with a single column that is the primary key Here is the create table statement: CREATE TABLE `test` ( `phone_number` varchar(18) collate utf8_unicode_ci NOT NULL, KEY ...
0
votes
1answer
72 views

Improve performance of query with many joins

From the following explain plan: explain SELECT imei, cliente_avl_equipo.id_cliente AS idcliente, cliente_avl_vehiculo.grupo AS grupo, avl_vehiculo.id, ...
0
votes
1answer
79 views

What is the cause of high index fragmentation in SQL server?

We have an application which is using a SQL Server database. As the application is in use for many years, its database is of huge size, resulting in slowness of application. It was noticed that the ...
2
votes
1answer
76 views

MySQL performance problem

I have the following medium size table (less than 3M rows) on a 1 core, 8GB RAM server: +-------------+----------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
2answers
150 views

slow query on mysql innodb table with 2 million rows

I have a table with about 2 million rows (growing at about 1 million every 3 months) and am having trouble with slow queries. I've tried changing my query and indexes (ensuring left most order) to no ...
1
vote
0answers
59 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 ...
3
votes
0answers
49 views

Influence of index rebuild tasks in tempdb on the transaction log?

I have read pretty much about index maintenance and all the pros/cons of reorg, rebuild, online, offline, TEMPDB and timing and right now I am kind of more confused than ever before... The goal is to ...
6
votes
1answer
163 views

Why is my SP_HELPINDEX showing a lot of indexes?

Using sp_helpindex My_table I have this list: But my table has only these indexes: Why is this happening? I'm using a query to search for "equal indexes" in a particular table. This query is ...
0
votes
1answer
46 views

Very slow query, not sure if MySQL index is being used

I have a query which includes a join and I have an index on the tables: SELECT e.entry, e.user_id from entries e left join users u on e.user_id=u.user_id where e.tenant_id=? AND u.active=1 AND ...
0
votes
1answer
43 views

What to do if all columns should be queried

I'm currently developing an application which saves the progression of a player during an online game. Which means, I want to store for example his score, played time, Flag Captures etc. All in all ...
2
votes
1answer
203 views

Is there a possible reason for having index on (a) and on (a,b) at the same time [duplicate]

Suppose I have a table with several column, among them - columns a and b. Is there any use case for which having both multi-column index on (a,b) and a single-column index on (a) is beneficial? ...
0
votes
1answer
28 views

Index Rebuilding Log File Growth

I would like to start running regular index maintenance on a server that has been running for a while without it. The database is running in FULL recover with log shipping. The problem I am having is ...
2
votes
4answers
197 views

Copy indexes from one table to another

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm ...
0
votes
1answer
31 views

Unused Postgres sequence values

Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the ...
0
votes
2answers
101 views

PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?

I have the following PostgreSQL schema: CREATE TABLE User ( ID INTEGER PRIMARY KEY ); CREATE TABLE BOX ( ID INTEGER PRIMARY KEY ); CREATE SEQUENCE seq_item; CREATE TABLE Item ( ID ...
0
votes
2answers
69 views

How to know if a table has a non-unique clustered index?

Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query? Thanks
0
votes
0answers
147 views

Full text search - Search with contains plus wildcard (*) not working properly

Although I know sql, I am a web developer. So this is a pretty new to me. I am working on a bug in a webapp regarding Full text Search, and some inconsistent results with it. You can see what I've ...
1
vote
1answer
78 views

Postgres reindex on crash folklore

I'm struggling with a problem of folk memory re Postgres. The folklore I am told that a long time ago in a galaxy far, far away (PostgreSQL 8.4 under Ubuntu 12.04), PostgreSQL frequently suffered ...
5
votes
1answer
1k views

Understanding “bitmap heap scan” and “bitmap index scan”

I'll try to explain my misunderstandings by the following example. I didn't understand fundamentals of the Bitmap Heap Scan Node. Consider the query SELECT customerid, username FROM customers WHERE ...
0
votes
1answer
35 views

Why my index don't update?

I have created this index ORACLE/VIDEO# create index titolo_index 2 on titolo(titolo) 3 indextype is ctxsys.context 4 ONLINE 5 ; I do a research select * from generale where ...
3
votes
1answer
106 views

Select performance problems with conditional index vs non-conditional on large volume table

We are trying to change some indexes on a large-volume table to be conditional indexes (function-based indexes) to reduce how much space is being used for indexes against this table. When testing the ...
2
votes
1answer
117 views

Hash index use case in MongoDB

Recently MongoDB added hash index to its features but in the documentation doesn't explain much about hash index use case and its privileges to normal MongoDB index. So I'll be grateful if you guide ...
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 ...
0
votes
1answer
48 views

MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct ...
2
votes
3answers
221 views

Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system. To illustrate the question, two examples would be: users, events, ...
0
votes
0answers
43 views

Help with fulll text index of large 100gb DB (yago) in MySQL

I'm trying to import the a full dump of Yago into a mysql (actually MariaDB -- I'm running an up-to-date arch system) database. The data is available here: ...
1
vote
2answers
82 views

How to determine the best index on a table with 3 nullable columns?

I am examining a users table similar to this: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `password` ...
1
vote
1answer
117 views

How can I rebuild an index on table with 100 million rows without a wait time out?

I have the following table with ~100 million rows: CREATE TABLE Result ( MetricId int NOT NULL, PeriodId int NOT NULL, Value decimal (18, 2) NOT NULL, CONSTRAINT PK_Result PRIMARY KEY ...