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)

2
votes
1answer
61 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE ...
0
votes
1answer
59 views

Do mongodb's 2dsphere index works on multidimensional coordinantes array?

if i create a index like that : db.createCollection("users") db.users.ensureIndex({coordinates:"2dsphere"}); and then insert a point var point = { type: "point", coordinates: [ 1, 2, 3, 4 ] ...
0
votes
0answers
181 views

Database Design for group chat

I am creating a chat system as a RESTful API. This is what my design looks so far A request can be made to fetch the newest 50 messages that are part of a group id. Even if I do get a list of ...
3
votes
1answer
83 views

Aggregation GROUP BY bottleneck

I have a performance bottleneck with a SELECT GROUP BY operation. Schema I have a table like this: CREATE TABLE [InverterData]( [InverterID] [bigint] NOT NULL, [TimeStamp] [datetime] NOT ...
1
vote
1answer
81 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) ...
4
votes
1answer
408 views

Why did adding a secondary index not increase select performance?

I have a table in MySQL called messages, which looks like this : id (primary key) | description | created_at(timestamp) This table is supposed to hold chat messages between users of my ...
7
votes
1answer
182 views

Index not used with `=any()` but used with `in`

Table t has two indexes: create table t (a int, b int); create type int_pair as (a int, b int); create index t_row_idx on t (((a,b)::int_pair)); create index t_a_b_idx on t (a,b); insert into t ...
3
votes
1answer
68 views

Does the principle of having an “ever increasing index” also apply for NVARCHAR and VARCHAR?

This might sound like an amateur question, but I just need to be 100% sure and I haven't found anything on it online. It is considered good practice having an "ever increasing index". Now, does that ...
3
votes
0answers
106 views

What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?

According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately ...
6
votes
2answers
779 views

Effect of an index on update statements where update column is not in an index

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute. While tuning my database to improve performance, I keep ...
6
votes
1answer
369 views

What happens when we add an index to an existing table with a large amount of data?

I've a table which will contain around ~15 Million records. Now I need to add an index to the table. Adding an index will take some time to update every entry in the table. I'm quite confused ...
1
vote
1answer
64 views

Store Mysql Index in separate .Ibd file?

Is there any option to store table index in separate .ibd file? something like file_per_table mode? Becuase my table is growing more than 200gb. Table .ibd file have index for that table also. so If ...
-1
votes
1answer
57 views

Verify this TSQL to find the indexes fragmented over 50%

select OBJECT_SCHEMA_NAME(IPS.object_id) 'schema', OBJECT_NAME(IPS.object_id) as table_name,index_type_desc, round(avg_fragmentation_in_percent,2) 'avg_fragmentation_in_percent', si.name ...
4
votes
1answer
126 views

Index not being used in SELECT query

I have a table of about 3.25M rows with the follow format in Postgres 9.4.1 CREATE TABLE stats ( id serial NOT NULL, type character varying(255) NOT NULL, "references" jsonb NOT NULL, ...
3
votes
1answer
164 views

Do WHERE IN queries reindex the table after each set within the query?

I have a basic WHERE IN query that takes too long (30+ secs) to process. DELETE FROM `table1` WHERE ( djID, localID ) IN (("128","26"),("110","32")) The table I'm querying on has about 20 million ...
4
votes
2answers
105 views

Optimize a query with small LIMIT, predicate on one column and order by another

I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times: Query #1 EXPLAIN ANALYZE SELECT posts.* FROM posts WHERE posts.source_id IN ...
13
votes
3answers
1k views

Why would SQL Server ignore an index?

I have a table, CustPassMaster with 16 columns in it, one of which is CustNum varchar(8), and I created an index IX_dbo_CustPassMaster_CustNum. When I run my SELECT statement: SELECT * FROM ...
8
votes
2answers
224 views

Are columns which are not indexes, sorted on disk together with index?

Are columns which are not indexes, sorted on disk together with index, in MySQL, in MyISAM and InnoDB? An incorrect thought that I started to write: I think that probably not, since they are not ...
5
votes
2answers
134 views

Rebuild index in SQL Server does not reduce fragmentation

I am trying to reduce the fragmentation of my indexes. First of all I rebuild all indexes with fragmentation higher than 30%, but the most of my indexes still with high fragmentation or does not ...
4
votes
1answer
52 views

Index not being used for logical operation

We have a MySQL table with a few million rows. We have set of users identified by a flag which int value. We are facing a problem that mysql is not using index. For example, following query is very ...
3
votes
1answer
47 views

Fastest way to find (rare) rows with a null value among millions of rows?

If I have an addresses table with ~ 4 million rows in a Postgres database. Is there a way to optimize the following query so that I can find the few rows where address_id is NULL? Sometimes I need to ...
1
vote
1answer
104 views

SELECT Causes Performance Issue [closed]

I have a SQL SELECT statement that has joins on 27 of our warehouse tables. Original statement was taking more than an hour everyday so I decided to optimize it. I created some indexes but still it ...
3
votes
3answers
142 views

Manually create index fragmentation

Does anybody have a good approach to manually create a specific fragmentation to an index, ideally without increasing the number of rows in the underlying table? I need this for the purpose of a ...
3
votes
1answer
54 views

MySQL: Add column to unique index or add new index?

For a MySQL database, I have an InnoDB table name user_metrics with the following columns: id INT(11) NOT NULL AUTO_INCREMENT, userid INT(11) NOT NULL, name VARCHAR(32) NOT NULL, value ...
7
votes
1answer
154 views

Improving a DbGeography query

I am still new to database administration and I'm trying to optimise a search query. I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU ...
4
votes
2answers
74 views

Indexed but still slow during query

I have a table that stores REST requests and responses: CREATE TABLE ws_call_logs ( id BIGINT NOT NULL, ws_method_id BIGINT NOT NULL, device_id ...
1
vote
0answers
27 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 ...
4
votes
1answer
236 views

Do indexes get transferred with pg_restore

I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore). However, it looks like it did in my recent dump/restore and I am ...
0
votes
0answers
31 views

Covered vs Noncovered Index & CRUD Sprocs question

I have what is a very simple question for most here I am sure. I would like to point out while i can run a query with the statistics on and get the answer, I would very much like an "under the hood" ...
3
votes
1answer
54 views

Index not being used in PostgreSQL

I have three tables, call them table1, table2 and table3. table1 has a foreign key column referencing table2, and table2 has a column referencing table3. Both foreign key columns have a B-tree index ...
3
votes
1answer
44 views

B Tree and B+Tree differences

I'm studying B+ Tree and B Tree and I would like to understand two things about it, if someone can clarify it to me I would appreciate: Why can I store more search keys on an B+ Tree Index? My guess ...
7
votes
1answer
152 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
69 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
108 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
55 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
45 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 ...
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, ...
1
vote
1answer
95 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
112 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
53 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
78 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
492 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
80 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 ...
11
votes
1answer
184 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, ...