Consider the following database tables: Table "messages" with 13,000,000 rows (one row per message). Table "users" with 3,000,000 rows (one row per user). The following query is used to fetch a bunch of messages and the corresponding users: SELECT messages.id, messages.message, users.id, users.username FROM messages INNER JOIN users...
Obviously, an index would work well if a column has a few distinct values. Will creating an index on a column where nearly every entry is unique be effective (such as a created_on (DATE) column)? ...
I have a table watchlist containing today almost 3Mil records. mysql> select count(*) from watchlist; +----------+ | count(*) | +----------+ | 2957994 | +----------+ It is used as a log to record product-page-views on a large e-commerce site (50,000+ products). It records the productID of the viewed product, the IP address and USER_...
In a SQL Server 2005 database, I have lots of tables like this Products table ProductID (PK) ProductCategoryID (IX) Description Price ExpiryDate BreakableYN ...where there is a primary key, a foreign key and then a bunch of other fields. Another characteristic of this type of table is that lots of queries only use the 2 ID fields (Pro...
I have a question about SQL Server indexes. I'm not a DBA and assume the answer is clear for those of you that are. I am using SQL Server 2008. I have a table that is similar to the following (but has more columns): CREATE TABLE [dbo].[Results]( [ResultID] [int] IDENTITY(1,1) NOT NULL, [TypeID] [int] NOT NULL, [ItemID] [in...
I have a fairly simple query: SELECT col1, col2… FROM dbo.My_Table WHERE col1 = @col1 AND col2 = @col2 AND col3 <= @col3 It was performing horribly, so I added an index on col1, col2, col3 (int, bit, and datetime). When I checked the query plan it was ignoring my index. I tried reordering the columns in t...
Is a table intrinsically sorted by it's primary key? If I have a table with the primary key on a BigInt identity column can I trust that queries will always return the data sorted by the key or do I explicitly need to add the "ORDER BY". The performance difference is significant. ...
So the main program is in C#. Inserting new records into a VFP database table. It was taking too long to generate the next ID for the record via select max(id)+1 from table , so I put that code into a compile dll in VFP and am calling that COM object through C#. The COM object returns the new ID in about 250ms. I then just do an ...
I use SQL Server 2005. I have a simple logging table that my web application uses to track user activities and urls visited. The table design is very simple ID (identity value), LogDate (datetime), Activity (nvarchar(200)), Url (nvarchar(1000)) We mainly do Inserts into this table. Once in a while, our perform some queries against ...
Can anybody know how to remove all the indexes from database ? ...
Hello erveyone, I am new to SQL Server 2008 fill factor, as mentioned here in SQL Server 2008 BOL, http://msdn.microsoft.com/en-us/library/ms177459.aspx My 2 confusions, Whether fill factor applies to index page? Or applies to both index and data page? At the beginning, seems fill factor applies only to index page -- "The fill-facto...
When I run SQL Server 2005 Database Tuning Advisor, it gives a recommendation to create an index, but it will recommends to index a column which already has an index on it. Why does it give a recommendation to create the same index again? Here is my SQL: SELECT t.name AS 'affected_table' , 'Create NonClustered Index IX_' + t.name +...
One day I suspect I'll have to learn hadoop and transfer all this data to a non-structured database, but I'm surprised to find the performance degrade so significantly in such a short period of time. I have a mysql table with just under 6 million rows. I am doing a very simple query on this table, and believe I have all the correct in...
Now, there are lots of ordered numeric vectors(about 50 dimension). Each dimension is a integer between 0~200. I want to sort them to ensure the similar vectors in the same bucket, all vectors in adjacent buckets have also a similarity to a certain extent. e.g. <1,24,25,78,9> and <2,24,24,78,9> should be in same bucket(bucket number is 0...
when i remove primary key constraint then SQL automatically remove the cluster index and same for unique it will remove non-cluster index ? ...
Hi all, I'm using Lucene .NET I've got 2 threads, each one doing indexing of some different content (using a different algorithm, although they might try to index the same document). They are both writing to the same index (using a single IndexWriter instance). Also, I've got a web application that also needs to write to the index occ...
Hi, I am trying to create a program using VBA that queries my oracle database data (in this case pipelines) against a spreadsheet and produces an output of the tie-out on the same workbook (but on another sheet). I would like to use the INDEX and MATCH functions on the tie-out page but have trouble figuring it out. Here is what I have so...
In SQL Server 2000, how do I check if a non-clustered index exists on a single column of table? ...
I have inherited a database where there are clustered indexes and additional duplicate indexes for each of the clustered index. i.e IX_PrimaryKey is a clustered index on the column ID. IX_ID is a non clustered index on the column ID. I want to clean up these duplicate non clustered indexes and I wanted to check to see if anyone could t...
My .NET application needs to access specific file types without searching the entire system. So, I would like to implement a file indexer so that I can access the list of files of a particular type easily. Which is the most optimal way to save the file details for faster searching based on file types. Is there any free library available...