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
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
32 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
41 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
73 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
86 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
186 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
64 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 ...
6
votes
1answer
172 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
48 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
204 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
224 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
32 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
111 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
73 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
171 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
85 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
36 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
114 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
148 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
54 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
224 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
46 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
127 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 ...
1
vote
1answer
137 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 ...
2
votes
2answers
150 views

Create index with a commentary

I would like to add some comments to my indices. So I added some additional information to the index creation script, expecting to see this comment again as soon as I open the script again. But it was ...
1
vote
1answer
85 views

Low cardinality fields: integer or string

A Ticket has the statuses: new in_progress on_hold closed I could make ticket.status a string (on_hold) or an unique int(2). It is indexed. Int pros: smallest size in index Int con: low clarity ...
2
votes
0answers
24 views

Combine several missing index suggestions with one index?

I investigate on missing indexes recommendations of my sql server engine. Looking at the most useful ones (for a specific table) that are candidates based on their potential usage and potential ...
1
vote
0answers
124 views

Does “CREATE INDEX WITH DROP_EXISTING” use the bufferpool for the recreated index?

We have recreate our 4TB clustered index in ouor SQL Server. Even thought we have 1.5TB of memory we still run into the following error message: "Msg 802, Level 17, State 2, Line 30 There is ...
1
vote
1answer
63 views

Understanding index fillfactor

I'm trying to understand the index FILLFACTOR parameter on a simple example. I have a table that is generally updated only once in ten days, but the update is quite heavy (requires 5-6 hours). Now, ...
3
votes
3answers
208 views

How is combined index implemented in SQL Server

I understand how single indexed column works in SQL Server and how it is implemented using balanced trees. There are plenty of interesting videos on YouTube on this topic. However I don't understand ...
0
votes
0answers
88 views

dropping index in a table that has 8 million entries

I am trying to execute the following: DROP INDEX idx_deletedAt_isLocked_testi ON app_instagram_shop_picture; DROP INDEX idx_id_deletedAt_isLocked ON app_instagram_shop; currently I am using mysql ...
4
votes
2answers
225 views

MongoDB uses only one index on simple query with sorting

So the problem is that I'm having a huge collection and I'm trying to run a query that filters and sorts on only fields that have indexes on them. I have these two indexes: ...
1
vote
1answer
65 views

Does InnoDB store row counts in secondary index B-Tree non-leaf nodes?

Does InnoDB store row counts of child nodes in secondary index B-Tree non-leaf nodes? If so, does MySQL use this information to speed up COUNT(*) queries with range constraint (assuming appropriate ...
1
vote
3answers
145 views

MySQL Indexing and performance when size of index is greater than size of data in table

I wrote this query for seeing if the size of the indexes in certain tables were larger than the actual amount of data in the table, using MySQL 5.6 and InnoDB as the engine. SELECT TABLE_NAME, ...
0
votes
1answer
39 views

How do I create a table best suited for storing address information

I have to create an address table in Oracle. Table is expected to hold millions of row. I created a table looking like this Table Address ID | Address Line1 | Address Line2 | Address Line3| City ...
1
vote
1answer
96 views

Is my postgres index being used?

I have a large PostgeSQL 9.1 database in production use. I want to free up some disk space. There is a large index, and I'm unsure if it's being used. select * from pg_stat_all_indexes where ...
2
votes
2answers
309 views

Very slow query in PostgreSQL on 10kk records table

I'm having very slow requests to my queries. I created an index on the filter columns and made partitioning but requests is still too slow. How can I make it faster? Here's query and explain: SELECT ...
0
votes
1answer
78 views

Improve execution time of query in POSTGRES with multiple joins

I'm having the trouble with the following query due to the growing size of the database ( websites is around a million rows, misspelled goes up to a couple millions ) It can reach up to 165 seconds ...
0
votes
1answer
62 views

dm_db_index_physical_stats results inconsistent with actual object properties - what may be the reasons?

SQL Server 2008 RTM (10.0.1600.22) Running a typical index fragmentation report on a table: SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, s.index_type_desc, ...
1
vote
1answer
78 views

how to check whether or not queries are doing partition elimination when accessing my partitioned table?

I am considering partitioning a table. I am trying to think in terms of how we query that table so that I achieve partition elimination. Suppose I have a table that has a partition by month, what ...
1
vote
2answers
89 views

Partitioned Tables and Indexes - what are the downsides?

When talking about Partitioned Tables and Indexes for tables with less than 100 partitions, no nonaligned indexes: with that I mean: Nonaligned index An index partitioned ...
0
votes
3answers
67 views

How to optimize SQL with WHERE and ORDER BY

I have the SQL statement: SELECT erp_orders_id FROM erp_orders o WHERE o.orders_export_time >= '2015-09-20' ORDER BY o.erp_orders_id ASC LIMIT 1 where erp_orders_id is primary key of type int. ...
1
vote
1answer
72 views

Search efficiency of datetime vs date + indexed smallint (PostgreSQL)

Context We are storing weather forecasts as multiband rasters (using Postgres with PostGIS). Each forecast is identified by its date and time (either 0:00, 6:00, or 12:00). We are looking at about 20 ...
2
votes
3answers
199 views

Excluding clustered key columns from non-clustered indexes definitions

As I've read the bookmark or the reference of the non-clustered index to the table clustered index is the clustered index key itself. On the SQL Saturday event I've attended, one of the lecturers said ...