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)

0
votes
1answer
52 views

Query bytea column using prefix

Assuming I have a table with a bytea column called data on a table called t, how can I: Write a query that returns all rows which have a specific prefix on their data column. Index the query. I am ...
0
votes
1answer
61 views

If I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?

We have an issue with MySQL InnoDB performance. We insert records into a table with a status field value of zero, these records need to be processed by another task. When this another task starts ...
7
votes
1answer
107 views

Index SEEK is not used unless OPTION (RECOMPILE)?

(Question moved from SO) I have a table (dummy data) with clustered index contains 2 columns : Now I run those two queries : declare @productid int =1 , @priceid int = 1 SELECT ...
1
vote
1answer
24 views

Postgres odd behavior with indices

I've got a datavalue table with ~200M rows or so, with indices on both site_id and paramter_id. I need to execute queries like "return all sites with data" and "return all parameters with data". The ...
2
votes
2answers
175 views

Clustered Index Creation

I have been trying to create a clustered index on a table which is 1.4 TB in size. This is my query: CREATE CLUSTERED INDEX [C_IDX_Impressions] ON [dbo].[Impressions] ( [User_ID] ASC ) WITH ...
4
votes
2answers
69 views

PostgreSQL ignoring (unique) index on TEXT field

I have a users table similar to CREATE TABLE users ( id bigserial NOT NULL, msisdn text NOT NULL ); and a UNIQUE constraint defined on msisdn: ALTER TABLE users ADD CONSTRAINT UNIQUE (msisdn); ...
1
vote
1answer
31 views

MySQL indexing issue when trying to search for a part of a string/word

What we're running into: Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We ...
3
votes
1answer
64 views

Choosing good indexes for my queries

I have a table which looks like this: Messages table: Message_ID (BIGINT) PK auto_increment FromUser (INT 16) ToUser (INT 16) DateCreated (Timestamp) MessageText (Varchar (500) ) HasRead (TINYINT ...
10
votes
3answers
278 views

Field order in a composite index order with high selectivity and low selectivity fields

I have a SQL Server table with over 3 billion rows. One of my query takes an extremely long time so I am considering optimizing it. The query looks like this: SELECT [Enroll_Date] ,Count(*) AS ...
4
votes
2answers
69 views

Performance for MySQL InnoDB B+Tree index with many duplicate values

I'm trying to diagnose seemingly random performance issues with our database server. Below is a simplified scenario, hopefully generic enough to serve as a useful future reference for anyone looking ...
1
vote
1answer
113 views

How can I improve this table, and subsequently the performance of the queries I execute on it?

I have inherited this table of POS transaction level data. In my opinion it is terrible. There are 3.5 million rows, which span ~4 years, with a distribution of anywhere between 1 result and 100k ...
5
votes
2answers
164 views

Total billions of rows and daily millions of row insert: What database system suits best [closed]

We developed a system with very high volume of data but very simplified data structure. We had only cellX, cellY, timeStamp and value columns. Operations are: Insert 455+ thousand rows per day ...
3
votes
1answer
88 views

Using ILIKE with unaccent and with only right end wildcard

I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with ...
6
votes
1answer
107 views

Index Allocation Maps

I am a developer reading the book "Querying Microsoft SQL Server 2012." I have come across the following diagram: Why are the pages in the first two rows of the diagram (row 1 starting: 1:174 and ...
1
vote
0answers
57 views

Cardinality of an index in a MySQL table is equal to table's row size

I have just included an index to a MyISAM table and the cardinality appears to be 1 million something which is exactly the number of rows in the table. In fact it needed to be 315 because distinct ...
2
votes
1answer
39 views

How do I recover my database after losing a tablespace with indexes?

I'm thinking of moving all the indexes in my PostgreSQL 9.3 database to a tablespace on an SSD for improved performance. This SSD is not in a RAID array and I need to consider a scenario where it ...
2
votes
2answers
317 views

SQL Server : Included columns in an Index: is there something like “too many”?

We have a table in one of our databases with approximately following structure: Field1 int primary key Field2 int foreign key A Field3 int foreign key B Field4 int foreign key C Field5 ... Field70 ...
0
votes
1answer
30 views

Splitting the table to get more clusterization options - will it help? [closed]

Complete theory. Let's assume we have a table with a few millions rows. The table has clustered index over PK, and it is important for quick fetching of particular rows. However, the table also has a ...
3
votes
0answers
87 views

Sort operation SQL Server 2012

I am trying to optimize a query and the sort operation (91% cost) is the costliest operation. Any help will be appreciated. Query is : SELECT TOP 1 ArchiveType, CONVERT(NVARCHAR(30), ...
0
votes
1answer
54 views

Full text indexes on big tables

I have a news website where I store my articles in a table that looks like that: Id title data Where data contains the content of the article and is the biggest column in the database by far. ...
0
votes
1answer
28 views

How to create unique index for both cases

I have table in PostgreSQL with sender_id and receiver_id and a unique index: add_index :my_table, [:sender_id, :recipient_id], unique: true Table will created, if sender_id: 1, and recipient_id: ...
7
votes
6answers
922 views

Fast reads without paying the price on writes

We have a large append-only table containing financial transactions. On average 1000 transactions are inserted per minute. Since there are now more and more usecases where we actually want to read, ...
3
votes
1answer
51 views

What indexing to be used over string columns(text type) with string length around 3000 characters

I am using open source columnar dbms (postgresql+Cstore). I am trying to speed up the response time of queries. My database has tables with strings mostly. String columns contain huge data (up to ...
3
votes
1answer
51 views

Fill Factor: Will the fill factor change automatically after it detects the changes in the index pages?

So lets say that the default fill factor is left at 0 initially. Data has been loaded up into the server and one fine weekend, the index rebuild job is run. Now if it detects any new data which must ...
2
votes
1answer
65 views

Can I use an index to optimize select clause, versus where clause?

I'm not sure if this question has been answered, because I'm not sure of the proper wording to describe what I need. So here it is: I am trying to optimize a database that is running very slowly, ...
2
votes
2answers
51 views

Is there any point with a combined index on all columns in a table?

I have stumbled upon several cases of indices set on all columns of a table. In my mind this will be as efficient as the full table scan that would occur without the index. Is this correct or is there ...
2
votes
1answer
130 views

How to estimate reindexing time?

How do you estimate the total time reindexing an entire table will take in PostgreSQL? I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 ...
0
votes
0answers
8 views

How to estimate PostgreSQL reindexing time? [duplicate]

How do you estimate the total time reindexing an entire table will take in PostgreSQL? I have a large PostgreSQL database, the primary table of which has 15 million records and 100 columns, and 200 ...
9
votes
2answers
426 views

Unused Indexes Best Practices

Based on this query, if I see a low amount of total reads (very close to 0 or 0, like 1 or 2) and a high or moderate amount of user updates (I couldn't find inserts or deletes with this query) with a ...
0
votes
1answer
66 views

Oracle over-indexed tables

I am trying to improve performance of a view in Oracle 11g, the view joins 11 tables, the 3 biggest tables in the query are 40, 27, 19 milions of rows. I found that each of these big tables has more ...
2
votes
1answer
51 views

How to create an index to be picked by query

I have created multiple indexes one after another, but my query doesn't want to pick them :( CREATE INDEX x ON gan.paid (project_id, country_iso_code, source, date(created_at)); CREATE INDEX x ...
0
votes
0answers
44 views

Is there an equivalent of virtual columns in mysql?

Currently I'm taking a look at amazon's new aurora database and I'd like to check how my current setup performs there. The original table is on maria DB and contains a virtual column ...
9
votes
1answer
139 views

Why can my index seek estimate the right number of rows and the sort operator can't?

I've a query that uses a function on the predicate, something like this: commentType = 'EL' AND commentDateTime >= DATEADD(month,datediff(month,0,getdate()) - 13,0) I have a filtered index on ...
0
votes
0answers
31 views

Why is my spatial index not being used on Mariadb?

I have a spatial index on geo column, but when I do EXPLAIN it doesn't show that the index is being used. Can anyone tell why? EXPLAIN SELECT AsText(geo) FROM marker WHERE ...
9
votes
5answers
290 views

Getting a scan though I expect a seek

I need to optimize a SELECT statement but SQL Server always does an index scan instead of a seek. This is the query which, of course, is in a stored procedure: CREATE PROCEDURE dbo.something ...
1
vote
0answers
53 views

Query with join to a table on a different database need an index

I'm creating a query that one of its joins is made to a table from a different database. The query analyzer is suggesting the creation of an index, however I don't have permission to add objects to ...
0
votes
1answer
62 views

Oracle index usage

I came across V$SEGMENT_STATISTICS dynamic view. What statistic should I consider to tell if index block is being updated from time to time (on the contrary to scenario where index block is created ...
2
votes
3answers
88 views

MySQL query takes over a minute to complete

I am trying to pull calls associated with website visits from our huge database... I have got SQL that works, pulls correct results it just takes over minute to complete... Here is the SQL SELECT ...
2
votes
0answers
34 views

MySQL Performance issue: ~360ms to fetch 120 rows with 10 columns

I have the following tables: CREATE TABLE `events` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'draft', `publish_at` datetime DEFAULT NULL, ...
1
vote
0answers
57 views

Mysql slow copying to tmp table to indexing

I try to indexing table. The table size is about 60GB. But indexing query is hang on 'copying to tmp table' 18 hours. I found advice setting 'myisam_sort_buffer_size' myisam_sort_buffer_size : 128M ...
4
votes
4answers
407 views

Is there any scenario where the existence of one or more Indexes in a table can prejudice it?

The title sums it up. I've learned and always heard that indexes in tables improve CRUD operations. A developer that I met last weekend told me that he does not like Indexes because they are bad - ...
0
votes
1answer
54 views

sqlite database table with 1000's of columns

I am using sqlite3 in an application to conduct an alalysis of distances between sets of points. Distances between these points have been precomputed using dijkstra's algorithim and left me with a ...
1
vote
1answer
88 views

Slow query performance on large table with no unique keys

I have a table that has approximately 20 million records and I'm having a very hard time improving the performance of the following query. It's taking well over a minute to run. SELECT ...
4
votes
2answers
144 views

Will the Query Optimizer Ignore A Fragmented Index?

Scenario: I have a heavy OLTP table with an index. I see many inserts, updates and deletes and the index fragments heavily within a day or less. While on day one of the index build, the optimizer ...
0
votes
1answer
32 views

Why is MySQL choosing that index?

I have a large indexed table with over 35 million rows. Here are the indexes, with a naming convention that shows their sequential column order: IDX_E93438363DAE128B, ...
0
votes
1answer
68 views

Innodb full table Scan

mytable is an InnoDB table with (a, b) as the primary key. Does this query check all table? Select * from mytable where ( b='2' and c='3') I want to find out if 'cluster' structure ...
0
votes
1answer
62 views

Creating index using dynamic SQL [closed]

I have following code for creating gist index CREATE INDEX web_stats_project_107_properties_crosspromo_gist_idx ON web_stats_project_107 USING gist (properties) WHERE properties @> ...
1
vote
0answers
56 views

PostgreSQL doesn't use index on inherited tables

I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS: an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
0
votes
1answer
47 views

MySQL optimizer difference between 5.6.27 and 5.5

We've just updated the MySQL version of the database of one of our clients from the 5.5 to the 5.6, everything is working fine except one simple query which is causing us troubles. Having the ...
0
votes
1answer
42 views

Will this SQL snippet REINDEX my GIN index? Why is it renaming an old index onto another?

I have a large PostgreSQL 9.3 database. One index on one table is quite large and appears to be bloated. I am using pgcompact from pgtoolkit to reduce database bloat without heavy table locks. However ...