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.
3
votes
3answers
3k views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
39
votes
6answers
8k views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
13
votes
5answers
3k views
Working of indexes in PostgreSQL
I have a couple of questions regarding working of indexes in PostgreSQL.
I have a Friends table with the following index:
Friends ( user_id1 ,user_id2)
user_id1 and user_id2 are foreign keys to ...
16
votes
7answers
394 views
Where can I find some guidance on index strategies?
Most of us will probably agree that using database indexes is good. Too many indexes and performance can actually be degraded.
As a general rule, which fields should be indexed?
Which fields should ...
7
votes
3answers
480 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
7
votes
4answers
4k views
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
I had to write a simple query where I go looking for people's name that start with a B or a D :
SELECT s.name from
spelers s
WHERE s.name LIKE 'B%' or s.name like 'D%'
order by 1
I was wondering ...
6
votes
3answers
4k views
Why is my database still fragmented after I rebuilt and reindexed everything?
I have a database which I tried to defragment all the tables at once by running this T-SQL:
SELECT
'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
'ALTER INDEX all ON ' + ...
5
votes
3answers
1k views
What is the default order of records for a SELECT statement in MySQL?
Suppose you have the following table and data:
create table t(k int, v int,index k(k)) engine=memory;
insert into t values(10, 1), (10, 2), (10, 3);
When issuing select * from t where k = 10 with ...
5
votes
1answer
617 views
Does MySQL still handle indexes in this way?
Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index.
If a ...
8
votes
2answers
696 views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
7
votes
2answers
233 views
Must an index cover all selected columns for it to be used for ORDER BY?
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
3
votes
3answers
638 views
InnoDB vs MyISAM with many indexes
I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway:
I have a ...
8
votes
2answers
2k views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
4
votes
2answers
285 views
Less RAM than Index_length MyISAM
I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint ...
2
votes
1answer
173 views
select for update gives error on indexed column
here is my code:
mysql_query("start transaction");
$q="select max(id) from test for update";
$r=mysql_query($q);
$row=mysql_fetch_array($r);
for ($k=0;$k<100000;$k++)
{
$q="insert into test values ...
12
votes
4answers
4k views
When should I rebuild indexes?
When should I rebuild the indexes in my relational database (e.g. SQL Server)?
Is there a case for rebuilding indexes on a regular basis?
4
votes
4answers
2k views
MySQL: Index when joining to tables not being used (Performance optimizing question)
I need to optimize the following query:
SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid
FROM blogposts
JOIN articles ON articles.blogpost_id = blogposts.id
WHERE ...
17
votes
4answers
4k views
Why does MySQL not have hash indices on MyISAM or InnoDB?
I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
16
votes
3answers
5k 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 ...
9
votes
1answer
306 views
Behavior of data in indexes based on fill factor
Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%?
From my understanding, when the data is inserted there ...
8
votes
3answers
2k views
How do indices impact query performance?
Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
11
votes
3answers
732 views
Is there any benefit to defragmenting SQL indexes in a SAN environment?
Our SQL server lives on a SAN. It contains dozens of OLTP databases, some with several tables containing over 1m records.
We have been running Ola Hallengren's index maintenance scripts weekly, and ...
7
votes
2answers
2k views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
3
votes
2answers
74 views
Algorithmic order of table indexes for table operations
I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations.
I am not interested in the nuts & bolts of specific implementations; ...
2
votes
2answers
160 views
MySql - find redundant indexes
is there a tool that spots redundant indexes in MySql?
e.g. if I have the following indexes:
index1(col1)
index2(col1,col2)
then index1 should be flagged as redundant.
any ideas?
4
votes
2answers
205 views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about 1MM ...
4
votes
2answers
587 views
Why does MySQL choose this execution plan?
I have two queries,
select some_other_column
from `table`
order by primary_index_column asc
limit 4000000, 10;
and
select some_other_column
from `table`
order by secondary_index_column asc
...
3
votes
2answers
163 views
MySQL InnoDB Index in swap
I can't find any info regarding my question. This is more theoretical question.
For example, i have table keywords.
CREATE TABLE IF NOT EXISTS `keywords` (
`kid` int(11) unsigned NOT NULL ...
2
votes
1answer
160 views
Improve query performance with index
I am looking to create an index for a database to acheive better performance.
I am using this query:
SELECT DISTINCT t1.* FROM current_order
AS t1 LEFT JOIN
receipt AS t2 USING ...
1
vote
1answer
86 views
Combining columns in index
With reference to RolandoMySQLDBA's answer on
MySQL: Index when joining to tables not being used (Performance optimizing question)
He mentioned
Make sure you build an index that involves the ...
0
votes
2answers
138 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: ...
14
votes
3answers
890 views
Is it OK to blindly add missing indexes?
I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
20
votes
4answers
2k views
What does “index” mean on RDBMSs? [closed]
I use indexes like most developpers do (mostly on... well ! index), but i'm sure there is a lot of subtle way to optimize a database using index. I'm not sure if it is specific to any implementation ...
11
votes
2answers
830 views
Is there any tangible difference between a unique clustered index and a clustered primary key?
I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
9
votes
1answer
707 views
What's the anatomy of a columnstore index?
One of the new features in SQL Server 2012 codenamed Denali is the Columnstore index.
I know a good bit about regular old row-store indexes, like the b-tree structure, differences in storage between ...
3
votes
1answer
2k 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 ...
5
votes
2answers
978 views
Why index REBUILD does not reduce index fragmentatation?
I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It ...
4
votes
1answer
151 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
4
votes
2answers
128 views
Custom unique column constraint, only enforced if one column has a specific value
Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is "true", then ...
4
votes
1answer
652 views
How can I get status messages during a large re-indexing operation with MySQL 5.5?
I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and ...
3
votes
1answer
354 views
Possible INDEX on a VARCHAR field in MySql
I am working in a MySql database, with a table like this:
+--------------+
| table_name |
+--------------+
| myField |
+--------------+
...and I need to make a lot of queries like this (with ...
3
votes
1answer
4k views
How to use psql with no password prompt?
I have wrote a script to REINDEX many indexes in a database. Here is one of them:
echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE}
psql -U ${USERNAME} -h ...
3
votes
3answers
863 views
When do hash indexes become reasonable?
MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking.
And it seems to be widely understood that if the text field is ...
2
votes
1answer
69 views
Does updating a partially indexed field add or remove it from an index in Postgres
We can create a partial index for a field in Postgres.
I am looking at using this to stop rows which are archived from being in an index on a created_at field:
First we create the index:
test=# ...
2
votes
2answers
501 views
IN Clause causes Execution plan to change from Nested Loops to Hash Match
I'm tuning a query and have discovered some behaviour I'm not clear about.
If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.
There only 7 rows returned in the ...
2
votes
4answers
501 views
Benefits of BTREE in MySQL
What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage?
Does BTREE provide easier iteration in increasing order ?
What kind of queries would ...
1
vote
2answers
177 views
How do I force a JOIN to use a specific index in MySQL?
I have a query that JOINs 2 tables; lineitem and part,
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem force index for join (l_pk),
part
where
(
...
1
vote
1answer
382 views
MySQL: Unique constraint on large column
I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column.
MySQL appears to ...
9
votes
1answer
3k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
9
votes
4answers
565 views
Where should one put indexes in a time dimension table?
After reading the Questions and Answers from this website about indexes, a question came to my mind.
What if, one is using a time dimension table with the lower level of granularity being the day. ...
