Indexing databases
if we make an index on the primary key of a table how would it increase performance ,,,,as if the desired record afer creating the index might turn up in the end of the index list!! ...
indexing
Why is my Primary Key slowing down my queries?
Hello, i'm using SQL Server 2008 and, in one of my tables, i implemented a (clustered) primary key on its identifier. Here's the table description: Identifier: - IdentifierId int NOT NULL PK - Alias nvarchar(200) - DataType int NOT NULL I made two indexes: one for Alias and another one for DataType. However, I just noticed somethin...
PHP default document (index.php) displaying code as plain text in IIS7
Hi, I've recently setup PHP 5.2.9-2 on IIS 7 on Windows Server 2008 SP2. Most everything seems to be working fine -- PHP is running great, just about all PHP code runs perfectly... EXCEPT for default pages. Any default document (index.php) is not interpreted by PHP and the page merely displays the underlying PHP code in the browser. Be...
Cannot create DB2 index, getting SQL30081N error
Trying to create an index (and run some long queries) on DB2 v9.1 and failing with the following error message: SQL30081N (A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "". Communication function detecting the e...
Why are my SQL indexes being ignored?
We're having a problem where indexes on our tables are being ignored and SQL Server 2000 is performing table scans instead. We can force the use of indexes by using the WITH (INDEX=<index_name>) clause but would prefer not to have to do this. As a developer I'm very familiar with SQL Server when writing T-SQL, but profiling and perform...
when were index statistics last updated?
Is there a quick and easy way to list when every index in the database last had their statistics updated? The preferred answer would be a query. Also, is it possible to determine the "quality" of the statistics: FULLSCAN, SAMPLE n, etc. EDIT this worked for what I needed, a slight mod to @OrbMan great answer... SELECT STATS_DATE(...
MySql Foreign Key index
Hi, I am using a Mysql table products which has a foreign key category_id on the categories table the foreign key constraint is defined (Innodb engine). I noticed that when I run EXPLAIN SELECT * from products where category_id=1; it uses the foreign key, so I see type=Range and Key: my_foreign_key But when I run EXPLAIN SELECT * f...
SQL Server - Adding an XML Index to a calculated column on a View
I have a table which stores comma separated values in an NVARCHAR(MAX). I have created a view, which uses string manipulation to convert these comma separated values into an xml list. I can then use this Xml column access each item. The queries on this column will benefit greatly if I could index it. However, on trying to create a Prim...
Is there a postgres fuzzy match faster than pg_trgm?
I have a Postgres table with about 5 million records and I want to find the closest match to an input key. I tried using trigrams with the pg_trgm module, but it took roughly 5 seconds per query, which is too slow for my needs. Is there a faster way to do fuzzy match within Postgres? ...
What type of index in best for DATE type on Oracle?
Hi, Basing on your experience with Oracle, what will be the best type and settings for index that you would set on a column of DATE type? I don't necessarily need to go for partitioned index. It is a logging kind of table. You don't really care about unique id as a primary key (in fact date is close enough to be uniques most of the ...
Does it make sense to put an index on tiny tables
I have a few tables with 4 to 10 rows. We don't anticipate that these tables will ever grow much more that a few more rows. Does it make sense to put an index on their primary keys. ...
Using an enum as an array index in C#
I want to do the same as in this question, that is: enum DaysOfTheWeek {Sunday=0, Monday, Tuesday...}; string[] message_array = new string[number_of_items_at_enum]; ... Console.Write(custom_array[(int)DaysOfTheWeek.Sunday]); however, I would rather have something integral to so, rather than write this error prone code. Is there a bu...
Indexes in a Data Warehouse
I'm creating a data mart in SQL Server 2008 using SSIS for load, and SSAS for an OLAP cube. So far, everything is working great. However, I haven't created any indexes on the source database other than the default clustering on primary key. I'm pretty comfortable with designing indexes on the application databases, but since this da...
how to automatically determine which tables need a vacuum / reindex in postgresql
i've written a maintenance script for our database and would like to run that script on whichever tables most need vacuuming/reindexing during our down time each day. is there any way to determine that within postgres? i would classify tables needing attention like this: tables that need vacuuming tables that need reindexing (we find ...
Is a bitmap index the best choice for a range query?
I'm trying to choose between these query plans for a range query: Sequential table scan Bitmap index B+ tree index Hash index My instinct is that a bitmap index would work here based on what I've read. Does that sound right? ...
why index not used here in MYSQL?
Very odd as follows: mysql> explain select *from online where last < now()-interval 30 second and type=1; +----+-------------+--------+------+---------------------------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ex...
1 index for 2 kinds of queries below possible in MYSQL?
select xx from tablexx where type in (1,3) and last<current-interval 30 second; select xx from tablexx where type=1; If create index on (type,last),the first one won't use index. If create index on (last,type),the second one won't use index. As for data type,which is can be seen from the example,type: int unsigned,last: datetime ...
Using solr for indexing different types of data
Hi. I'm considering the use of Apache solr for indexing data in a new project. The data is made of different, independent types, which means there are for example botanicals animals cars computers to index. Should I be using different indexes for each of the types or does it make more sense to use only one index? How does using ...
How to identify if a Lucene.Net Index exists in a folder
I am using Lucene.Net for indexing and searching documents, and I am using the following code to create or open an index if one exists: IndexWriter writer = new IndexWriter(@"C:\index", new StandardAnalyzer(), !IndexExists); private bool IndexExists { get { return ?? } } now how can ...
Lucene.Net Best Practices
What are the best practices in using Lucene.Net? or where can I find a good lucene.net usage sample? ...