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)

3
votes
0answers
41 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
2answers
24 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
36 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 ...
0
votes
1answer
16 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 ...
4
votes
1answer
816 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
0answers
11 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
198 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
64 views

How does indexes work in a database? [on hold]

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
39 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
43 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?...
3
votes
2answers
59 views

MySQL Online DDL Index Creation

I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is ...
0
votes
2answers
28 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 ?
3
votes
1answer
37 views

Is DROP/ADD UNIQUE constraint in ALTER TABLE atomic?

I have a UNIQUE constraint of a number of fields on a table. Image that I want to change the fields in this constraint (remove one field). Normally I would do something like: DROP INDEX unique_name ...
12
votes
2answers
421 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 ...
1
vote
0answers
13 views

Get missing index statistics in SQL Server 2014 [duplicate]

For example consider a database that has many SP and many query execute on it using ORMs. It's very time consuming that check every SP or profile all queries that ORMs generate for performance and ...
0
votes
0answers
19 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 ...
3
votes
1answer
32 views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
4
votes
1answer
74 views

Foreign Key Constraint Set Back to Trusted

We have some production tables with numerous constraints that are not trusted at the moment. We're having them checked/trusted so they can be used again. We know that the optimizer has not been ...
12
votes
1answer
256 views

Clustered index not used in a delete statement

I have a SQL Server table defined as follow CREATE TABLE [dbo].[Production_Detail] ( [Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]), [Meta_Data_ID] INT NOT NULL ...
1
vote
1answer
49 views

SELECT count(*) from a materialized view with unique index is slow

I have a materialized view with a unique index (on the id column) and both of these queries are slow (they scan the whole table): SELECT count(DISTINCT a.id) from the_view a; SELECT count(*) from ...
96
votes
6answers
32k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
5
votes
1answer
888 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 - ...
4
votes
0answers
74 views

Why different datetimes freezes a query based on indexes?

I have a table with a billion records. It has these columns: Id bigint, MobileNumber varchar(100), Date datetime, Message nvarchar(400) And it has non-clustered indexes on MobileNumber and Date ...
0
votes
1answer
146 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?
0
votes
0answers
31 views

Does having a .ldf for a index file cause issues?

Interesting find today, I have a file group that is used for indexing. It has one file that has a .ldf extension, which as you know for SQL Server, is the extension for the transaction log files. My ...
25
votes
3answers
29k views

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an ...
0
votes
2answers
41 views

Query to retrieve index details for a particular column in all tables

There is a column created_at in most of tables. I would like to get the details if that field exists in the table and it is not indexed. Is there any thing can be done with the information_schema to ...
1
vote
3answers
6k views

How do you correct a corrupted index in Oracle?

I have this ORA error in our logs: Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: '/data/app/oracle/oradata/MYSRVR/...
3
votes
2answers
173 views

Partitioning a large table has not improved performance, why?

In SQL Server 2014 I have partitioned one of my large tables weekly and defined a sliding window scenario to switch the oldest week's data to the archive DB and create a new partition for the next ...
1
vote
2answers
28 views

MySQL CREATE INDEX slowing down

My analytics table has 190M rows, about 150GB ins size. Storage engine is MyISAM with key-buffer-size=16G myisam_sort_buffer_size=2G myisam-max-sort-file-size=200G. Machine has 32GB memory. While ...
0
votes
1answer
159 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 ... ...
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
1answer
184 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 ...
4
votes
2answers
439 views

Clustered vs Nonclustered indexes, similar to arrays vs linked lists?

I'm just starting out as a DBA, and have recently been interested in indexes and how they speed up queries. I was reading about indexes here: https://msdn.microsoft.com/en-us/library/ms190457.aspx ...
1
vote
4answers
247 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 ...
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 ...
3
votes
2answers
208 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....
0
votes
1answer
61 views

Building MySQL InnoDB clustered indexes

Assuming that you have a table like CREATE TABLE `t1` ( `search_id` int(11) NOT NULL, `provider_id` bigint(20) unsigned NOT NULL, `status` text NOT NULL, `imported_at` int(11) NOT ...
1
vote
0answers
18 views

Primary key index not used when using inner join with jsonb_array_elements subquery on Postgresql 9.5

I have two tables: hub and huboperator hub table is defined as CREATE TABLE public.hub ( parent_hub_id integer, ancestors jsonb, descendants jsonb, name character varying(1024), id integer ...
1
vote
0answers
50 views

Clustered Columnstore Index on Table with Foreign key

Using SQL Server 2014, I am trying to create a clustered columnstore index on a table which is referenced by a foreign key. This is the syntax I am using CREATE CLUSTERED COLUMNSTORE INDEX ...
0
votes
2answers
127 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
3answers
55 views

Why full index scan over full table scan?

I have a query that pulls from small tables in MySQL: SELECT C.cid, C.email, R.flight_number FROM customer C INNER JOIN reservation R ON C.cid = R.cid WHERE C.email LIKE '%d.com'; There exists an ...
3
votes
1answer
54 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
0
votes
1answer
60 views

Effect of Reorganizing and Rebuilding an index [closed]

What are the effects of Index Rebuilding and Reorganizing on clustered and non-clustered indexes. How does these 2 operations affect the logical storage and physical storage of data in Hard disk?
0
votes
2answers
286 views

List full text indexes in Oracle database

Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index. How can I do something similar using Oracle? Is it possible?
1
vote
1answer
249 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
1answer
32 views

Mysql read only engine type and indexes

I have a mysql table. This table has only three columns. Two are varchar (5),and one is varchar (32). There will only be one query run against table. Select (column 1),(column 2) from mytable where ...
8
votes
1answer
1k views

MSG 666 when running an insert query on 80M-row indexed table

Strangely, my stored procedure started to receive Msg 666 for some input data. Stored procedure fails on the last step when it tries to insert a row into a table with the following structure: ...
1
vote
1answer
35 views

Count approximation with where clause

I have the following problem, we have a table of facilities for apartments that looks something like this: I would like to perform the following query SELECT crawled_name, count(f.id) AS count ...
1
vote
2answers
41 views

Dynamic script to Drop Indices of a specific column

I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column). In my script I have ...