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
131 views

Using an enum index to limit records

A common issue, just not sure on the keywords. I have a table of assessments (14k+ records). Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done'). Most ...
1
vote
0answers
4k views

How to speed up query on table with millions of rows

The Issue: I'm working on a big table that consists about 37mln rows. Data include measurements of many devices made in certain time e.g. '2013-09-24 10:45:50'. Each day all of those devices are ...
1
vote
1answer
55 views

Creating Multiple Indexes on SQL Server [closed]

I am creating multiple indexes on a table in SQL Server. The script is still executing but all the required indexes are showing as objects in Object Explorer. Do the objects get created before the ...
3
votes
2answers
9k views

What is difference between primary index, secondary index, unique index and normal index?

I work on Oracle systems where I two type of indices exists in any table: Unique index (Based on any search key which is unique key). Normal index (Index made on any search key which is not ...
0
votes
1answer
90 views

How to store values of all columns in an index in PostgreSQL?

As described here: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes indexes do not store the entire row data themselves. Is it possible to store the whole row ...
2
votes
2answers
127 views

Optimize query or model, to get an population overview in time

I think I've got a very simple model and query, but I cannot find a way to make use of indexes to boost performance. I wonder if there is a trick in my model, or MySQL query, to speed up the ...
1
vote
2answers
138 views

Is this compound index unnecessary?

I have a large table which contains sensor data, along with the fields: sensor_id timestamp I do queries against it using these fields almost exclusively. There are multiple sensors, and different ...
5
votes
1answer
3k views

Improve speed of index rebuild on SQL server

I'm importing a large amount of data into an empty database, and before I start I disabled all non-unique non-clustered indexes to see if I could improve the performance of the import. Now I want to ...
0
votes
1answer
3k views

Find the size of each index in a MySQL table

I am trying to figure out size of each index of a table. SHOW TABLE STATUS gives "Index_length" is the summation of all the indices of the table. However if a table has multiple indices (e.g. in an ...
1
vote
2answers
418 views

How to improve performance if index is not Most SELECTIVE?

As per suggestion index must be most selective to improve the performance. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table's rows The ...
6
votes
1answer
736 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
257 views

MySQL looking up more rows than needed (indexing issue)

In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows: +----------------+-------------+-----------------+--------+ | participant_id | question_id | given_answer_id | status | ...
1
vote
0answers
35 views

table scheme definiation it this way that insert duplicate into another table

I have a table in which there is a column name with SP varchar(10) NOT NULL. I want that column always to be unique so i created unique index on that column . My table schema as follows : CREATE ...
1
vote
1answer
210 views

Combining multiple left join for one single line

We have a query as below. SELECT tblSData.header, tblEAlert.eMessage FROM tblSData LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID Where tblSData.aID=".$aID." Order By tblSData.dateTimer ...
1
vote
1answer
149 views

Benefit to keeping a record in the database, rather than deleting it, for performance issues?

So I have a client that I am building a Rails app for....I am using PostgreSQL. He made this comment about preferring to hide records, rather than delete them, and given that this is the first time I ...
18
votes
2answers
8k views

Does a re-index update statistics?

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is: Does a re-index update the statistics? We found discussions online ...
0
votes
2answers
312 views

Find missing indexes

tool available to find missing indexes in mssql 2008 ? can we find the missing index using Database Engine Tuning Advisor
1
vote
1answer
1k views

Unable to drop non-PK index because it is referenced in a foreign key constraint

I have a table named MyTable. The primary key is an identity int column named MyTableID. There is a unique clustered index on the PK column MyTableID named PK_MyTable. I noticed there is an ...
2
votes
2answers
573 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 = ...
0
votes
1answer
58 views

Optimizing a union query

SELECT id FROM user WHERE id IN ( 2,3,4,7,9,21,23,45,324,12,43,254 ) UNION SELECT DISTINCT user_id FROM workgroup_users WHERE workgroup_id IN ( 1,345,34,654,213,123,5,2,3,4 ) I know we should not ...
0
votes
1answer
286 views

SQL Server 2008 - Question about index behaviour

I have a general question about advanced issues regarding index behaviour. In short, roughly one year ago, we dynamically dropped and recreated a number of indexes with different filters, but using ...
0
votes
1answer
184 views

How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?

I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like: add_index :users, [:username, :created_at] That should ...
0
votes
2answers
96 views

Understanding multiple indexes with MySQL (and performance)

I have a "phones" table with around 120 000 records, it contains phone_id, phone_number, phone_user_name, phone_last_contacted, etc. The phone_id is index and looks like: 1,2,3,4 ... 120 000. But I ...
6
votes
3answers
7k views

Why does MySQL ignore the index even on force for this order by?

I run an EXPLAIN: mysql> explain select last_name from employees order by last_name; +----+-------------+-----------+------+---------------+------+---------+------+-------+----------------+ | ...
0
votes
2answers
587 views

How to index for a query with independent range conditions?

What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?
0
votes
1answer
381 views

MySQL MyISAM index causes query to match no rows; indexes disabled, rows match

I created a table and index as described in this SE post, but when I query the table for a particular ID, no matches are found. When I disable the index, matches are found. Commands ran: CREATE ...
1
vote
1answer
690 views

How to partition an Oracle table with 65 million rows?

The table that needs partitioning contains a list of Components and their Values. So one table is tblComponents. Another is tblFields. Then this table, tblValues, contains the values of the ...
7
votes
4answers
343 views

how to explain indexes

This question is about teaching: When I learned databases, we talked about indexes being like the card catalog in the town library. We had at least an author index, a title index, then the Dewey ...
2
votes
1answer
998 views

Index for speeding up sorting by boolean, timestamp

For a website that displays recent news, I have this query on the home page: SELECT * FROM "cms_news" WHERE NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00') AND ...
1
vote
0answers
248 views

Fulltext search returning too many rows

Server: MySQL 5.1.66-0+squeeze1 My problem: I have two databases with the same tables. One is for the testing environment and one for production. When I perform a MATCH AGAINST on the prodution ...
1
vote
1answer
209 views

In MySQL if column X has unique values what's the difference between UNIQUE index and B-Tree index

Let's assume that I have table data: CREATE TABLE `test`.`data` ( `row_id` INT NOT NULL , `data_id` INT NOT NULL , PRIMARY KEY (`row_id`) ); Now, let's assume that data_id has unique values. Is ...
3
votes
2answers
8k views

Index for date column for range query in Oracle?

We have a lot: select count(*) from TBL where XDATE between ? and ? and FLD = ?; We expect to boost performance by tuning index on XDATE column (which have DATE type). What type of indexes more ...
0
votes
2answers
239 views

Full text catalog does not allow me to hit on singular forms

I have created a full text catalog for a column under database name -> "Storage" -> Full Text Catalogs. It is accent sensitive and "default catalog" is set to false. The language for the column ...
2
votes
0answers
384 views

PostgreSQL index array of int4range using GIN / GIST - custom operator class

Here is my table: CREATE TABLE mytable ( id INT NOT NULL PRIMARY KEY, val int4range[] ); I want to index the val column: CREATE ...
1
vote
1answer
81 views

What indexes to add on MySQL table

I've been trying to optimize my table, but am yet unable to get the desired results. CREATE TABLE IF NOT EXISTS `puzzles` ( `puzzleID` int(7) NOT NULL, `puzzleMask` varchar(30) CHARACTER SET utf8 ...
2
votes
2answers
350 views

Adding an index to a system catalog in Postgres

I'm having a situation very similar to the one described here: I've got a SaaS situation where I'm using 1000+ schemas in a single database (each schema contains the same tables, just different ...
0
votes
2answers
1k views

MySQL Cluster - Table is full [Error 1114] while still creating tables

I am trying to forward engineer a MySQL Workbench model with about 100 tables to a development single-node cluster and, while creating the 51th table, I end up getting a "1114 table is full" error, ...
1
vote
2answers
713 views

Avoiding a sort on an already clustered index for group by

On a table T with two fields, pid and did, the following query results in a seq. scan followed by a sort on pid: select count(did), pid from T group by pid Here is the query plan: ...
17
votes
2answers
4k views

LIKE uses index, CHARINDEX does not?

This question is related to my old question. The below query was taking 10 to 15 seconds to execute: SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] ...
2
votes
1answer
2k views

Query taking non-consistent time to execute [closed]

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
3
votes
2answers
790 views

Question about non-clustered index storage in SQL Server

I have a table people with two non-clustered indexes. create table people( id_person int, first_name varchar(50), last_name varchar(50), city varchar(100), state char(2), zip_code int ) CREATE ...
1
vote
1answer
184 views

Index method for very few updates and many inserts

I am using Postgresql 9.1 with *pg_trgm* extension. I need to create an index on a text-based field. I do not need full-text searches, I use ILIKE queries to make my searches. I will use pg_trgm but ...
3
votes
2answers
2k views

Should I refresh query plan cache

Please let me explain my problem and situation: I have a web application - MVC3, MSSQL Server 2005, LinqToSQL. It has been running great until one fine morning I pushed a lot of rows to a table that ...
1
vote
2answers
105 views

Using an index for both uniqueness and fast lookup?

Right now I have a postgresql 8.4 database set up for players in a multiplayer game. I want the username column to be unique. I also want to be able to lookup a player by username quickly. Here's the ...
7
votes
1answer
14k views

CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?

Just came across a weird issue, whereby depending on how I create an index, an index name is required. http://dev.mysql.com/doc/refman/5.5/en/create-index.html ...
4
votes
2answers
5k views

How to choose a collation for international database?

I'm designing a database which will store data in different languages (using UTF-8), so I think the best way to display the queries results is ordering it according to the user's language during the ...
1
vote
1answer
393 views

optimizing MySQL for traffic analytics system

background : I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as ...
5
votes
1answer
435 views

How can a fill factor of 0 or 100 be the same?

As of my understanding, Fill factor of 80 implies that 20 percentage of each leaf-level page will be empty to enable future growth. I can't correlate how fill factor of 0 and 100 can be the same! Am I ...
2
votes
4answers
846 views

When do I use another type index than integer? (eg string) [integer vs string index]

I was reading about relational databases like sqlite and I always see the schema for user storage like: UserID (index) | Username | Password but wouldn't it be actually much faster to use Username ...
1
vote
1answer
890 views

Would adding indexes to my foreign keys improve performance on this MySQL query?

Consider the following query: SELECT `locations`.`id` AS `location_id`, `locations`.`address`, `locations`.`lat`, `locations`.`lng`, `tickets`.`status_id`, `customers`.`name`, ...