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

Oracle 11g INDEX_SS Hint

Given a table with columns A, B, and C of type CHAR(15) and an unique index If I have a query with a WHERE clause specifying A and C, oracle's optimizer chooses to do an index range scan. But if I ...
0
votes
1answer
323 views

How can I speed up a DateTime comparison query in MySQL?

I am performing the following query on a MySQL table with around a million records ( ~350MB ). It takes roughly 1.5 seconds every time. If I remove the OR last_updated > last_geocoded comparison, ...
0
votes
1answer
251 views

SQL Server Index Scan Actual Executions

I have a fairly complicated query and running it on two databases with same schemas, but with different data produces different result. On database D1 the elapsed time is around 1 second, and the ...
0
votes
2answers
94 views

Rebuilding index

I have a transaction database and one of nightly job is to rebuild indexes that followed by a log backup and then shrink the log. Is this safe to do so ? Thanks Antony
0
votes
1answer
141 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 ...
0
votes
1answer
424 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 ...
0
votes
2answers
2k 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, ...
0
votes
1answer
403 views

Postgresql table with one integer column, sorted index, with duplicate primary key

I want to have a table in PostgreSQL with these properties: One column with an integer Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be ...
0
votes
1answer
375 views

Is index on foreign key used? (No difference in EXPLAIN.)

Essentially, I am joining two MyISAM tables and have found that the EXPLAIN output for the query does not change regardless of whether I have an index on the foreign key in the parent table. I'm not ...
0
votes
1answer
103 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
0
votes
1answer
128 views

Two identical MySQL tables, one not using indexes

I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data. Schema of one table CREATE TABLE `measuresHistory` ( ...
0
votes
1answer
44 views

Which text-index I should create for xooops engine to achieve better search results?

In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following : SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, ...
0
votes
2answers
1k views

Indexing a longtext field

I would like to run an index on a longtext field using: CREATE INDEX post_meta ON wp_postmeta (meta_value(8)); There are currently ~1 million records. Questions: Will creating this index affect ...
0
votes
1answer
993 views

Group By on column from joined table

its about the following query. EXPLAIN SELECT phrase, infinitiv FROM `dict_verbs_all` `t` INNER JOIN `dict_verbs_all_language` `verbsGerman_c` ON (`t`.`id`=`verbsGerman_c`.`verb_id`) INNER JOIN ...
0
votes
1answer
345 views

how to pass url value to index [closed]

How to read the value from URL and compare with index that i have created in MYSQL table. I have a form where provide list of selection using drop down list and i pass the selected value to the next ...
0
votes
1answer
748 views

InnoDB - Use combined index with primary key on GROUP BY

As far as I know, an InnoDB index always contains the primary index. I cannot get the following to work: SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid WHERE t1.b = 99 GROUP ...
0
votes
2answers
4k views

How can primary keys in a Netezza table be indexed?

What is the best way to index a Netezza fact table with lots of primary keys. For now, let's suppose we have 10 primary keys. This table is mainly used to store meta data about our database. ...
0
votes
1answer
160 views

The sort column must be the last column used in the index - MongoDB Indexing Advice

MongoDB docs advices that: The sort column must be the last column used in the index Here is the example: db.foo.ensureIndex({a: 1, b: 1, c: 1}) Good: find(a=1).sort(a) find(a=1).sort(b) ...
0
votes
1answer
960 views

slow index scan on postgresql 9.1

I have a view which is amazingly slow :) It joins two tables only, having a few simple WHERE conditions and one in a form of a subquery (see SubPlan1 below). The simplest condition is checked as an ...
0
votes
2answers
300 views

Attempting to avoid SORT in query planner

I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically): ...
0
votes
1answer
400 views

MySql - Global index

I'm new to MySql partitioning and have a question regarding indexes. let say i have the following table: CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, ...
0
votes
1answer
33 views

Creating index for large table is taking forever

I am trying to create Index for a large table with row count of 496,000,000 and the database is on SQL Server 2012 Enterprise edition. I know how to create index but its taking forever and would like ...
0
votes
1answer
46 views

Is unique index combined with a “not null” constraint equivalent to a PK constraint?

I have these legacy table definitions (from here): CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] ...
0
votes
2answers
45 views

LOB indexes corruption in Oracle

Why is it not possible to rebuild corrupt LOB indexes? So if my LOB index is corrupted, how to recover it?
0
votes
1answer
51 views

How to manage RAM consumption in MongoDB?

I am using MongoDB. I have a hardware device which continuously submits data to the database every 10 seconds for 4 hours a day. The data includes latitude, longitude, and other basic information of ...
0
votes
1answer
50 views

How to create index on long data type field

I have a MySQL table with a long data type field: `create_date` long NOT NULL, I want to index it: CREATE INDEX ix_logs_create_date ON `logs` (create_date) USING BTREE; But I encounter the ...
0
votes
0answers
76 views

How can i add index in MYSQL stored procedure?

I can not use alter table () on my stored procedure i don't have a permission to do that in the big database data_pro. How can i use index in my stored procedure for my 2 fields in where condition? ...
0
votes
2answers
34 views

List full text indexes in Oracle database

Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index. How can I do something similar using Oracle? Is it possible?
0
votes
1answer
63 views

MYSQL fulltext - What's the right way to get the relevance from one column?

I want to make one column more valuable than others like it's been done in query bellow.This example shows that only one fulltext index is needed And this answersuggests that three fulltext indexes ...
0
votes
0answers
46 views

In Oracle, when you update an SDO_GEOMETRY column with the same value is the MDSYS SPATIAL_INDEX updated?

Because generating and updating a spatial field is relatively expensive we want to avoid it when possible. Here is the relevant bit of our schema: +---------+---------------+ |LATITUDE ...
0
votes
2answers
33 views

Mysql query is using some other index

We have 2 tables: CREATE TABLE `allcallers` ( `id` varchar(50) NOT NULL DEFAULT '', `company_id` varchar(50) NOT NULL, `clid` varchar(30) NOT NULL, `clid_raw` varchar(50) NOT NULL, `uid` ...
0
votes
1answer
48 views

What data structure is used for MongoDB 3.x geospatial indexes?

For the MongoDB 3.x geospatial features what structure is used for the indexes? B-tree? R-tree? X-tree? I can't find any information about the structures in their documentation
0
votes
1answer
33 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
0
votes
0answers
72 views

Is there some tricks to better index a URL column in postgres?

I'm working with a table that needs fast retrieve rows by its url column. But they all will be starting with http://www.: http://www.google.com http://www.amazon.com http://www.stackoverflow.com I ...
0
votes
0answers
29 views

Trigger copies history table: Will removing index lock options speed up the process?

I have a trigger applied to a table to write inserts and updates to a history table. That history table can grow pretty fast, and we want to see, if we can speed up insert operation a bit. The values ...
0
votes
0answers
30 views

Postgresql indexing for IS NULL

I have two similar queries, whose only difference (in the last two lines) is that one uses IS NULL while the other compares defined values. Query #1 takes only a few minutes: UPDATE ...
0
votes
0answers
31 views

Indexing Multiple Possibly Missing Columns

I'm working with a particularly large database, and am not particularly in tune with some of the intricacies of RDBMS's as I work primarily in PHP/WP and with smaller databases. Either way, I have ...
0
votes
0answers
17 views

MySQL ignores ZWNJ character in string comparison

If you add a unique index to a char columns you can not add two string that differ only in having ZWNJ characters (e.g. 'فا' and 'ف‌ا'). However I need to store both of them. How can I overcome to ...
0
votes
1answer
54 views

Index Strategy for MySQL Query that matches on one column and between two others

I have a table that contains a set of measurements for a continuous stream of processes. Although each process is individual, they are categorised into groups. The events have a start and end ...
0
votes
1answer
100 views

Optimizing an Informix query — Specify architectural solutions if possible too (like caching service)

We have a huge query on an Informix database that is not performing well. The query is doing a join of 9 different tables. Maximum row count is just 40,000 rows. The query also has 9 sub-queries, and ...
0
votes
1answer
42 views

How to determine if an Index is uselss in Percona MySQL?

Using Percona Server 5.5 I've a table which stores user activty (lets call it user_activity). It was created quite a few years ago when I had only a little bit of knowledge about database ...
0
votes
0answers
72 views

Composite primary key in Multi-tenant sql server database

I'm building a multi-tenant app (Single database, single schema) using Asp Web Api, Entity Framework and Sql Server/Azure database. This app will be used by 1000-5000 customers. All the table will ...
0
votes
0answers
8 views

Need a persistent, glorified array / bucket storage solution

I'm looking to create a system that logs information ( think, lots of writes, and a few reads ) in order of entry within a key/value datastore. The order precision is absolutely necessary above ...
0
votes
0answers
33 views

PostgreSQL: raise error if planner needs to do an sequence scan

I want to check if our code does use only indexed columns. I think it does, but I want to be sure. How can I configure/modify PostgreSQL to raise an exception if it needs to do an sequence scan? It ...
0
votes
0answers
31 views

Optimal index wasn't being used until rebuilt

SELECT COUNT(T1.RECID) FROM LEDGERJOURNALTRANS T1 WHERE PARTITION=5637144576 AND DATAAREAID='abcd' AND JOURNALNUM='abcd-00018' This query gets run numerous times when users view a ...
0
votes
0answers
52 views

Indexing on cached data from related table using triggers — Is there a better way?

Edit: Schema details per request from Aaron Betrand -- I'll break the schema down to the relevant fields of the two tables. Orders (ID int IDENTITY, Status char(1)) Services(OrderID FOREIGN KEY ...
0
votes
0answers
47 views

How can I use PostgreSQL's partial sum tree index (if it even exists)? Do any databases allow for use of such an index?

GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ...
0
votes
1answer
35 views

MySQL 2 Million Rows, longtext and varchar indexes

I'm trying to find a solution for this, with no success. I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR. name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE ...
0
votes
1answer
39 views

InnoDB index concurrent inserts

I have an InnoDB table with an index on it. I have a few million rows to insert, so I thought I might take advantage of the row-level locking and do it in parallel. I'm inserting in batches of 10000, ...
0
votes
0answers
91 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...