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
2answers
254 views
What exactly is “pinning” in relation to indexes?
When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.
It is part of a test question, where ...
3
votes
1answer
261 views
Why does it take a long time to drop a function based index in Oracle?
I have a function based index that takes 25 minutes to create on a table of 94 Million rows.
When I drop the index, it takes 18 minutes.
Why does it take so long? I would have thought the drop would ...
3
votes
1answer
3k 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
2answers
2k views
MySql - How can I speed up this query
I have the following tables:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_name` varchar(255) COLLATE ...
3
votes
3answers
131 views
Optimizing queries on a range of timestamps (two columns)
I use postgresql-9.1 with ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
3
votes
1answer
73 views
How can I alter the fill factor of an index outside of an ALTER INDEX command?
I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB ...
3
votes
2answers
71 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; ...
3
votes
1answer
70 views
query discrepancies on very large postgres table
I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem.
these two queries although similar are taking massively ...
3
votes
2answers
149 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 ...
3
votes
1answer
192 views
Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning
I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
3
votes
2answers
776 views
SQL Server 2008R2 - Why is my index not used
I have a table defined in the following way:
CREATE TABLE [dbo].[MyTable]
(
[MyTable_ID] [int] IDENTITY(1,1) NOT NULL,
[COLUMN_WITH_DATA] [varchar](128) NOT NULL,
[COLUMN_A] [varchar](128) ...
3
votes
2answers
110 views
Does a dual key or single key lookup perform better on a table with a lot of data?
We have table where rows unique between 3 columns. GroupA, GroupB, ProductID.
GroupA & GroupB are used in combinations to Group sets of ProductIDs and will always be used to lookup sets of ...
3
votes
1answer
100 views
Do operations on indexed views happen in parallel?
When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel.
Is ...
3
votes
2answers
725 views
MySQL: Storing unique URLs
I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
3
votes
1answer
57 views
How can I get this query to use it's index?
Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime).
As I don't really know what I'm doing I tried adding indexes to all columns in ...
3
votes
1answer
103 views
Why is a hash match operator in this very basic query
I'm beginning to learn some about looking at execution plans and making queries more efficient
Consider these two basic queries
select distinct pat_id, drug_class, drug_name from rx
select pat_id, ...
3
votes
1answer
149 views
Query optimization with extreme statistic
I have a table:
mysql> describe table_1;
+---------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default ...
3
votes
1answer
126 views
I have Fragmented Index on a empty table!
I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be ...
3
votes
3answers
550 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 ...
3
votes
2answers
772 views
Postgres primary key sorted in the reverse order. Will it use the index efficiently?
My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed.
My list views show records in the reverse ...
3
votes
1answer
212 views
Will an index that is a partial key of the begining of another index perform any better than the longer index?
If I create an index that is a partial key of the begining of a second index, will SQL Server get any significant gains retreiving the results from the simpler index, when the search criteria matches ...
3
votes
1answer
564 views
MySQL ignoring index, index cardinality is the same as number of records in the table (?!)
Everyone, I could use an extra set of eyes on an issue we've come across. Here's what we're seeing:
We're writing some code to migrate our messages to a new table called
communications. We're ...
3
votes
1answer
196 views
MySQL text field without quote
I've just run into something strange. The base_id is a varchar(255). When I do a SELECT without quote, it scans the whole table:
mysql> EXPLAIN SELECT sid FROM rf_fo.scald_atoms WHERE base_id = ...
3
votes
1answer
299 views
Index fragmentation increase as more row being deleted
I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday. Index fragmentation was low.
This table has high transaction throughput. ...
3
votes
1answer
702 views
Oracle not using index when > used in where clause
We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date.
Because there is no end clause to the date range, oracle ...
3
votes
2answers
2k views
How to make CREATE INDEX in InnoDB table faster
I have platform RHEL 5 with 8G RAM and MySQL version 14.12 distrib 5.0.86. I want to create an index in an InnoDB table, but it takes a long time:
Time | State | Info
2291 | copy to tmp ...
3
votes
1answer
671 views
Estimating impact of indexes SQLite database size
I'm trying to estimate the database size (on disk) for a SQLite DB that includes a number of indexed columns. These columns are of (SQLite) type Integer and String. It's straightforward enough to ...
3
votes
1answer
123 views
Query not using index when it should
I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table?
...
3
votes
1answer
340 views
Indexing SQL Server datetime Columns for Performance
Will adding an index to a datetime column increase performance if a WHERE myDateColumn BETWEEN @startDate and @endDate clause is frequently applied to that datetime column?
If yes, should the index ...
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 ...
3
votes
1answer
161 views
Does creating an index on BIT(n) attribute in MySQL make sense?
I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2.
I've seen some mentions on the internet that BIT ...
3
votes
1answer
238 views
SQL Server: Duplicate Index Question
We recently has a massive performance problem on our system, which we narrowed down to a problem with I/O on the LUN. IOPS increased twenty-fold during the period when the problem was occuring. The ...
3
votes
1answer
98 views
should we replace some non-clustered indexes with clustered indexes
We have some data saved for reporting each of our tables currently has a unique id column that has an auto generated clustered index on the pk and a non-clustered index for the fk.
I am thinking that ...
3
votes
1answer
173 views
Minimum rowcount for filtered index?
Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known.
But with the advent of ...
3
votes
1answer
204 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
366 views
Very Slow MySQL Queries, even with indexes
I have a relatively large 4-deep relational data setup like this:
client_applications: (potentially 1,000's of records)
- ...
- account_id
- ...
3
votes
1answer
608 views
Should I rebuild index after truncate/large insert?
I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)
Basic outline is very simple:
...
3
votes
2answers
62 views
Looking for a possible multi-column index
I have a table highscore containing the columns:
game (text)
date (timestamp)
score (integer)
more irrelevant ones...
The query most often run on it is:
SELECT *
FROM highscore
WHERE game = ...
3
votes
1answer
129 views
For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?
Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns?
Like if I have this table:
CREATE TABLE [dbo].[tbl_shipping_boxes]
(
...
3
votes
1answer
635 views
How do I reindex a MYSQL table using a different disk?
I have my mysql data and index files on disk1
I need to reindex the table and do NOT have enough disk space on disk1
How do I reindex a table on disk1 and use disk2 for the reindex disk space?
For ...
3
votes
1answer
1k views
how to fetch different data on compare rows of two tables
I have two tables in my database, one is user_app table in which user info are stored, table are as:
+--------------+--------------+------+-----+-------------------+-----------------------------+
| ...
3
votes
1answer
182 views
Adding new key(s) to enable partitioning, INSERT performance?
I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 diferent devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
3
votes
1answer
3k views
Unique index corrupted SQL. Select query returns single row but create unique index fails
I am not able to repair my database due to below errors, when I run dbcc checkdb(DBNAME, REPAIR_ALLOW_DATA_LOSS) I am getting the errors listed here.
When I run a select query for these key values, ...
3
votes
1answer
711 views
Postgres full text search with multiple columns, why concat in index and not at runtime?
I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns.
The postgres docs talk about creating a ts_vector ...
3
votes
1answer
72 views
In MySQL, does the order of the columns in a WHERE clause affect query performance,why?
I have a query that doesn't use any indexes:
SELECT 32,
guid,
1,
1,
1,
0,
5
FROM test
WHERE level >= 20
AND ( ( fun_GetIndexValue(data, 354) ...
3
votes
1answer
179 views
Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field
This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
3
votes
0answers
72 views
Key Lookup and Full-text index
I have the following query:
SELECT T2.Title
FROM TitleTable T1
INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
AND T2.FKID2 = @ID_PARAM2
AND ...
3
votes
1answer
1k views
Oracle 11: Change global index to local on huge production table
I have big partitioned table with global index. My case is drop old data from archived partitions. I can't simply drop partition because i have global index.
So I have idea to use INVISIBLE local ...
3
votes
0answers
162 views
Can spatial index be a part of multiple columns index in mysql?
This manual over here is extremely unclear and doesn't even provide some sample SQL statements:
http://dev.mysql.com/doc/refman/5.6/en/column-indexes.html
Another way to rephrase the question is the ...
3
votes
1answer
149 views
index not being used
I have an exact same myisam table on master and slave. The indexes are not being used on Master server.
mysql> explain select count(date_time) from ox_data_summary_ad_hourly where date_time = ...
