indexing

Speeding up row counting in MySQL

Suppose, for illustrative purposes, you are running a library using a simple MySQL "books" table with three columns: (id, title, status) id is the primary key title is the title of the book status could be an enum describing the book's current state (e.g. AVAILABLE, CHECKEDOUT, PROCESSING, MISSING) A simple query to report how many...

Use Rails Migrations to delete an index without knowing its name

I have a table with a compound index that wasn't created through a rails migration. Now, I need to create a rails migration that will delete this index and create a new one, but I don't necessarily know what the name of the index will be. I know that it is possible to get a list of table names and column names within a migration step. I...

Adding indexes to gerrund tables

So, I have a subscriptions table: id - int(11) (With Primary Key) user_id - int(11) group_id - int(11) role - int(11) pending - tinyint(1) created_at - datetime updated_at - datetime I'm often doing queries to see if users have access rights similar to this: SELECT * FROM `subscriptions` WHERE (group_id = 1 AND user_id = 2 AND pendin...

Is a globally partitioned index better (faster) than a non-partitioned index?

I'm interested to find out if there is a performance benefit to partitioning a numeric column that is often the target of a query. Currently I have a materialized view that contains ~50 million records. When using a regular b-tree index and searching by this numeric column I get a cost of 7 and query results in about 0.8 seconds (with no...

Why does Lucene cause OOM when indexing large files?

Hi, I’m working with Lucene 2.4.0 and the JVM (JDK 1.6.0_07). I’m consistently receiving “OutOfMemoryError: Java heap space”, when trying to index large text files. Example 1: Indexing a 5 MB text file runs out of memory with a 64 MB max. heap size. So I increased the max. heap size to 512 MB. This worked for the 5 MB text file, but...

SQL Server Table Synonyms with Indexes

I have multiple databases on a single instance of SQL Server 2005. I've created a synonym on one database to access a table on another database and when writing my queries, I'd like to utilize a specific index, however, when evaluating the execution plan, it doesn't appear to use it. If I write the query to access the database explicit...

Searcing for a list of keywords to find out which ones exist or not

I have a large document with various sections. Each section has a list of keywords /phrases of interest. I have a master list of keywords/phrases stored as a String array. How can I use Solr or Lucene to search each section document for all keywords and basically give me which keywords were found ? I cant think of any straightforward wa...

Tools for searching offline website

I built a website which runs on localhost and is pretty big by now. What are the tools for indexing and searching my offline website ? For example if my website is placed in the directory c:\mywebsite\ , i just want to submit this directory to the indexing software/tool and then it should work something like Google. I want it to be funct...

drop index at partition level

Do you know if there's any way of doing this in SQL Server (2008)? I'm working on a DataWarehouse loading process, so what I want to do is to drop the indexes of the partition being loaded so I can perform a quick bulk load, and then I can rebuild again the index at partition level. I think that in Oracle it's possible to achieve this,...

Using robots.txt or another method to change the default page for Search Engines

We have a flash "gateway" page displayed to users (only once) before they enter our corporate website. This flash page is default.aspx, served as default page by the server. The issue is that Yahoo and other SE pick up the text from the body of the page, which happens to be the Javascript/Flash requirements warning in the noscript tag. ...

datamapper multi-field unique index

In Datamapper, how would one specify the the combination of two fields must be unique. For example categories must have unique names within a domain: class Category include DataMapper.resource property :name, String, :index=>true #must be unique for a given domain belongs_to :domain end ...

Correct implementation of an indexer on a derived class

I have a class, say DerivedBindingList<T>, which is derived from BindingList<T>. I would like to use an indexer with the derived class, and have coded it as: public T this[int index] { get { // Getter code } set { // Setter code ...

Can you use index in mysql using "col1 OR col2"?

I have a mysql query that gets a list of private messages where a user is either the sender, or receiver. SELECT users_user1.user_name AS pm_username_1, users_user1.user_avatar AS pm_username_1_avatar, users_user2.user_name AS pm_username_2, users_user2.user_avatar AS pm_username_2_avatar, pms.* FROM pm pms L...

SQL Server: Index columns used in like?

Is it a good idea to index varchar columns only used in LIKE opertations? From what I can read from query analytics I get from the following query: SELECT * FROM ClientUsers WHERE Email LIKE '%niels@bosmainter%' I get an "Estimated subtree cost" of 0.38 without any index and 0.14 with an index. Is this a good metric to use for anlayzi...

SQL Server: Alter a clustered primary index to an non-clustered

How do I alter a primary clustered index to become a non-clustured index. (Being a "secondary" table I want to use the clustured index for the foreign key column of the "header" table.) This doen't work for me (error seems reasonable :) DROP INDEX ClientUsers.PK_ClientUsers CREATE UNIQUE CLUSTERED INDEX IDX_ClientUsers_Id ON ClientUser...

MySQL does not use effectively use the index for ORDER BY queries

I have a simple Message table, with 2 indexes: mysql> show keys from Message; +---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | ...

SQL Server: How to best index an N-N Table?

I have a N to N table with columns: Id (primary key) (I need this for various reasons) ClientId FeatureId Queries use all combinations of ClientId and FeatureId and need to be quick for all cases. (ClientId+FeatureId, ClientId, FeatureId) My idea is to create a covered clustured index on ClientId and FeatureId and additional noncovere...

How to speed up this query?

ATM I am trying to learn how to efficiently use database inidices and would appreciate to get some expert input. I do not have any performance issues currently. I would just like to know, how you would handle your indices with this query: SELECT B.event, COALESCE(B.system, C.surname || ' ' || C.forename) AS name, C.label...

Oracle Text URL indexing performance

I have an oracle table that has two columns - id and url. The URL is simply http://somemachinename/mypage.php?id= I then create an oracle text index using the URL datastore on the url column. If I then do: BEGIN ctx_ddl.sync_index(idx_name => 'MY_INDEX', memory => '50M', parallel...

where i can find ServerConnectionManager/indexing server open source to learn

Hello i need to learn and later to implement Server Connection Manager to be used by peer to peer turn based game is there any resource to learn how to implement such architecture ? ...