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
1answer
11 views

best way for creating Index in mysql (multi-column/table)?

i have a big problem with my database.. I need a very fast query over 2 tables. 1. table: (node) ~42.500.000 entrys 2. table: (waynode ~51.200.000 entrys The following query is much to slow: ...
6
votes
1answer
12k 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 ...
0
votes
1answer
165 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
5
votes
2answers
919 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 - ...
0
votes
1answer
36 views

PostgreSQL indexing on bit string

What is the best way to create an index on a bit string column? Let's say I had the column of type bit(4) and I wanted to search for all entries that had had a specific bit set. So if I had the ...
1
vote
1answer
22 views

How to Create Indexes Or Manage Performance For Complex Queries Before a Release?

Last night we had a major release that involved a massive data dump and some complex queries. We semi-anticipated the slowness and were able to react quickly to creating some new indexes in SQL ...
0
votes
0answers
18 views

Count number of indexes usage for select

I have a lot of indexes. Some of them are not used (because they created once). But I want to know why it is not used. I have the following tables and indexes: create table statuses ( id ...
0
votes
1answer
162 views

Does remove document automatically remove the index that reference this document?

I am wondering if removing the document will automatically update the index if this document is referenced?
1
vote
1answer
73 views

Primary Index VS Unique Index Performance

I have the following table: CREATE TABLE `actions` ( `user_id_from` int(11) unsigned NOT NULL, `user_id_to` int(11) unsigned NOT NULL, `action_type` int(11) NOT NULL, `counter` smallint(11) ...
1
vote
0answers
35 views

what if all the non clustered indexes on my table were filtered indexes?

I have a table that is highly hit on my database. this table has 370,370 records. when I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [...
7
votes
4answers
10k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes --------+-----------...
2
votes
1answer
46 views

Make index work on join to table variable

I a table with several columns, but the most importants are: DocumentId(bigint), StatusId(int) and ReceiveDateTime(datetimeoffset(3)). CREATE TABLE [DocumentInfo]( [DocumentId] [bigint] NOT NULL, ...
0
votes
1answer
16 views

Using varchar_pattern_ops in a multi-column index in Postgres

I'm using postgres 9.5 If I have a table with 2 columns like so: CREATE TABLE mystuff ( somestring character varying(256), timestamp_ timestamp without time zone NOT NULL ) Will this multi-...
7
votes
1answer
235 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
vote
1answer
1k 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? ...
1
vote
2answers
55 views

mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command: ALTER [IGNORE] TABLE tbl_name [...
2
votes
2answers
2k 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 ...
0
votes
2answers
41 views

MySQL: Indexing by column which is also a part of a compound primary keys and

I have a table which has a compound primary key, but I would also like to query the table using one or more of the columns composing this primary key. For example: CREATE TABLE `user_visit_stats` ( ...
1
vote
1answer
185 views

Index to fix this query MySQL

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the ...
1
vote
3answers
20 views

Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

Consider a table created like this: CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT ...
4
votes
2answers
215 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', album....
1
vote
4answers
250 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
2answers
37 views

How to not replicate index creation on ignored tables in MySQL/MariaDB

I have a setup with two MariaDB masters, each being the slave of the other. Some tables are ignored, with the configuration option : replicate-ignore-table = moodle.mdl_temp_% My problem is when a ...
0
votes
0answers
17 views

Do I need to index JOINED columns even if they only appear in SELECT?

This is a reformatting of the question asked here: MySQL JOIN is fast, but selecting column is slow Do I need to add an index to EVERY column in a JOINED table if that column will appear in the ...
0
votes
0answers
25 views

MySQL JOIN is fast, but selecting column is slow

My logs table has half a million rows. Each row has an indexed log_level_id column pointing to table log_levels (with only 5 rows). On log_levels, id is an auto-increment primary key and label has ...
1
vote
1answer
82 views

Query for a month works faster then for a week

I have strange situation when query for a month works faster then for a week: There is a request for a month (2016-06-01 - 2016-07-08) that works quickly and uses proper index: SELECT `t1`.* FROM `...
1
vote
3answers
240 views

How to create a Unique (Index) constraint across multiple tables?

Is there a way to create a unique index across tables in a SQL Server database? I have two tables, Table A and Table B. In that I have a Column named ID, I want to make these Id columns as unique (...
5
votes
2answers
140 views

Optimizing “WHERE x BETWEEN a AND b GROUP BY y” query

CREATE TABLE test_table ( id uuid NOT NULL, "RefId" uuid NOT NULL, "timestampCol" timestamp without time zone NOT NULL, "bigint1" bigint NOT NULL, "bigint2" bigint NOT NULL, "int1" integer ...
1
vote
0answers
44 views

Why not create ColumnStore index on every single table?

As ColumnStore index improves performance for data warehouse databases, and also since SQL 2104, it is updatable too, why not creating ColumnStore index on every single table? Edit: In SQL 2014 we ...
0
votes
0answers
21 views

Mongo DB services went down while creating indexes in >300GB Mongo database instance

While creating index for a collection in Mongo DB instance.Mongo DB services went down with below error. kindly help on how to overcome the same.For smaller database instances it went fine. Command ...
1
vote
1answer
137 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 ...
0
votes
2answers
142 views

LOB indexes corruption in Oracle

Why is it not possible to rebuild corrupt LOB indexes? So if my LOB index is corrupted, how to recover it?
0
votes
2answers
45 views

Are two logically equal indices physically the same index?

I have a table with this definition: CREATE TABLE public.delete_operation ( id bigint NOT NULL, parent_id bigint, pdu character varying, progress smallint NOT NULL DEFAULT 0, description ...
1
vote
1answer
262 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not all)....
0
votes
2answers
31 views

Can i Make Index for all columns(No write operations)?

Few days I just learned indexes.I my New app(running on node js) There will no write operation.And my server has 500mb of ram.Can I set Index to all of My column.I almost have 500K rows.My query is ...
1
vote
0answers
32 views

How similar is using a secondary index in InnoDB to using a second table joining 'into' the PK?

Since InnoDB secondary indexes are separate B-trees with the key as the sort order and the PK as the 'value' stored, I assumed that reading into the main table by the secondary index was a lot like ...
3
votes
2answers
1k views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
13
votes
1answer
2k views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
1
vote
1answer
131 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 ...
1
vote
1answer
27 views

Mongodb performance: compound query vs moving to other collection

My application uses mongodb to store order information. At some point orders are sent after which I need to generate some reporting of all orders in a timerange. To give an idea of my order document: ...
1
vote
1answer
167 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
0
votes
1answer
26 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
2answers
50 views

Use of Non clustered Indexes

I know the differences between clustered and non-clustered indexes. Can someone tell me the various scenarios where we actually prefer non clustered index over a clustered index ?
1
vote
2answers
28 views

How can one identify if FK's needed to be indexed apart from the NC indexes already residing

I was going through many articles, which said indexing the FK's is beneficial for good performance, specially for the environment where most of the reporting is done. I have a similar env where there ...
0
votes
1answer
37 views

Use of nonclustered index [duplicate]

I have a table that does not have a primary key. This is because there is no unique field. So I created an index on SerNum, ScanDate so I could look at the table in SerNum, Scandate order. It appears ...
4
votes
1answer
843 views

Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans: select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 ...
1
vote
1answer
215 views

Using index on datetime field not working really

Here is my query select msisdn, sum(score) as score from contest_player_update_history_ideacontest6_35 where request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' ...
-4
votes
1answer
74 views

How does indexes work in a database? [closed]

If I have a list of name that are all different and I have to find a specific name,how could indexes help.thi research?How does index work in a database?I have read other discussion but I haven't ...
2
votes
1answer
43 views

Wordpress using varchar(255) for index with InnoDB and utf8mb4_unicode_ci?

I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for ...
0
votes
1answer
53 views

Set indexes for database used for login and register actions

I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?...