indexing

What indexes can be used to improve this query?

This query selects all the unique visitor sessions in a certain date range: select distinct(accessid) from accesslog where date > '2009-09-01' I have indexes on the following fields: accessid date some other fields Here's what explain looks like: mysql> explain select distinct(accessid) from accesslog where date > '2009-09-01'; ...

Limiting the results in IIS index catalog querying

I am using IIS index service to have a search feature in my website. I used the below code to build the query for results string query = String.Format(@"SELECT Rank, VPath, DocTitle, Filename, Characterization, Write FROM SCOPE('DEEP TRAVERSAL OF ""{0}""') WHERE NOT CONTAINS(VPath, '""_vti_"" OR "".pdf"" OR "".config"" OR "".js"" OR...

can i make web server to be index server ?

hello all i need to build index server for p2p (games) application can i just use web server(lighttpd) and extend it with some plug in? is there any problem with this method? ...

SQL Server index for cursors

I have an application that sometimes runs slow and I think it is related to database cursors. Don't have any access to the application source so I can't control the database calls but the database is open so I can add index where needed. Problem is that I don't know really know how to speed up cursors with index. The cursor queries a...

Sphinx search engine, a couple of quick questions.

So, I'm just starting to read up on this, I've never implemented a search in PHP before. I have a couple of questions that I was wondering: By the sounds of things, Sphinx needs a 'daemon', a program running in the background, to operate? Say I built an index of a mySQL table, then a user uploads another record. For the searc...

Setting up Sphinx

Hehe, don't laugh at me, just trying to set up Sphinx on my local WAMP, I haven't done this before so I'm probably doing something silly. This is my sphinx.conf file: source code { type = mysql sql_host = localhost sql_user = root sql_pass = sql_db = **** sql_port = 3306 sql_query = SELECT id, language_id, c...

Opening MySql database to search engines.

Hi, Most of my content on my web application gets stored in MySql database. I want to open this content for search engine to index it. What is the best solution to do this. Best could be either performance oriented or ease of implementation. Thanks in advance! ...

Help on understanding multiple columns on an index?

Assume I have a table called "table" and I have 3 columns, a, b, and c. What does it mean to have a non-clustered index on columns a,b? Is a nonclustered index on columns a,b the same as a nonclustered index on columns b,a? (Note the order). Also, Is a nonclustered index on column a the same as a nonclustered index on a,c? I was look...

Slow MySQL retreival of the row with the largest value in indexed column

I have a SQL table readings something like: id int client_id int device_id int unique index(client_id, device_id) I do not understand why the following query is so slow: SELECT client_id FROM `readings` WHERE device_id = 10 ORDER BY client_id DESC LIMIT 1 My understanding with the index is that mysql keeps an ordered list (one prop...

What column should the clustered index be put on?

Lately, I have been doing some reading on indexes of all types and the main advice is to put the clustered index on the primary key of the table, but what if the primary key actually is not used in a query (via a select or join) and is just put for purely relational purposes, so in this case it is not queried against. Example, say I hav...

SQL Server Index question

I have a query that joins 3 tables in SQL Server 2005, but has no Where clause, so I am indexing the fields found in the join statement. If my index is set to Col1,col2,col3 And my join is Tbl1 inner join tbl2 On Tbl1.col3=tbl2.col3 Tbl1.col2=Tbl2.col2 Tbl1.col1=Tbl2.col1 Does the order of the join statement make a difference as com...

MySQL: which is preferable, separate indexes or same index?

I'm trying to speed up SELECT queries in MySQL tables which already have some indexes. Hopefully without significant impact on frequent INSERT INTO queries running at the same time (by other clients). The tables have millions of entries and have already passed the Gigabyte size (they have a BLOB field). The tables have a pair of fields ...

Creating Indexes for Group By Fields ?

Do you need to create an index for fields of group by fields in an Oracle database? For example: select * from some_table where field_one is not null and field_two = ? group by field_three, field_four, field_five I was testing the indexes I created for the above and the only relevant index for this query is an index created for fie...

How to utilise indexes in SQL

I'm building a rather large database - which has around 6.9 million records. A simple select is taking 6-7 seconds, so I'm now working on optimising and investigating other options. An obvious one is to create an index or two. Sample: CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME) That worked well. However, I cannot get ...

SQLite3: programatically determine whether a column is sorted in ascending or descending order

Suppose I have an index on a table in SQLite3: CREATE TABLE Person (id integer primary key, firstName varchar(20), lastName varchar(20), address varchar(200)); CREATE INDEX IX_Person ON Person (lastName ASC, firstName ASC); I can discover which columns are in the index like this: sqlite> pragma index_info('ix_person'); 0|2|lastName 1...

Hyper reference links in Latex document starts from the beginning of the page

Hi, I have a latex document. I am using hyperref, makeidx and glossary packages for my document. Every thing is created fine; table of content (all references works nicely), glossary and index except that page numbers printed in the glossary and index are correct but they point to page numbers starting from the beginning of the documen...

How do I monitor and find unused indexes in sql database

I would like to monitor index usage for an sql database, in order to find unused indexes and then drop them. How can I monitor index usage most efficiently? And which scripts could be useful? (I'm aware of this question about identifying unused objects, but this applies only to the current run of the sql server. I would like to monitor ...

how does lxr (linux kernel code cross reference) work internall?

Where can I find an article to introduce the architecture of the LXR? I am very curious about it. How are the indexing and query served? Thanks, ...

How I can find the list of Sybase Indexes for a given database?

How I can find the list of Indexes for a given database in Sybase? ...

Do covering indices safely replace smaller covering indices?

For example: Given columns A,B,C,D, IX_A is an index on 'A' IX_AB is a covering index on 'AB' IX_A can be safely removed, for it is redundant: IX_AB will be used in its place. I want to know if this generalizes: If I have: IX_AB IX_ABC IX_ABCD and so forth, Can the lesser indices still be safely removed? That is, does IX_ABC ma...