index

Unable to create index because of duplicate that doesn't exist?

I'm getting an error running the following Transact-SQL command: CREATE UNIQUE NONCLUSTERED INDEX IX_TopicShortName ON DimMeasureTopic(TopicShortName) The error is: Msg 1505, Level 16, State 1, Line 1 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DimMeasureTopic' an...

TOP 2 faster than TOP 1 in Sql Server?

Hi, we have a table with +- 500k rows in Sql Server 2005 database and one of its columns has a full-text index. We were doing some tests and found that SELECT TOP 1 ... WHERE CONTAINS(fullTextColumn, 'anyValue') was taking more than two minutes to return. However the same select, but with TOP 2 instead of 1 return in a few seconds. A...

Why isn't index used for this query?

I had a query where an index was not used when I thought it could be, so I reproduced it out of curiosity: Create a test_table with 1.000.000 rows (10 distinct values in col, 500 bytes of data in some_data). CREATE TABLE test_table AS ( SELECT MOD(ROWNUM,10) col, LPAD('x', 500, 'x') some_data FROM dual CONNECT BY ROWNUM <= 100000...

Oracle: does the column order matter in an index?

An index on two columns can be created with either of the statements create index foo_ix on foo(a,b); create index foo_ix on foo(b,a); How does this affect the operational (runtime) characteristics of using the index? How does this affect the layout (physical) characteristics of the index? Are either (1) or (2) affected by the types/...

Show which columns an index is on in PostgreSQL

I would like to get the columns that an index is on in PostgreSQL. In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column. mysql> show indexes from foos; +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Tabl...

What is the best way to do server-side output caching in PHP?

I have a pretty complicated index.php now, and I would like to only run it once every hour. What is the best way to achieve this? Some ideas I've had Put it in APC with apc_store($page, 60*60*) - I feel this isn't what APC is for and will probably be doing something bad to the other parts of my site Save the output to a filesystem som...

Indexing table with duplicates MySQL/SQL Server with millions of records

I need help in indexing in MySQL. I have a table in MySQL with following rows: ID Store_ID Feature_ID Order_ID Viewed_Date Deal_ID IsTrial The ID is auto generated. Store_ID goes from 1 - 8. Feature_ID from 1 - let's say 100. Viewed Date is Date and time on which the data is inserted. IsTrial is either 0 or 1. You can ignore Order_ID an...

Merge PDFs and retain their index (xref-Table?)

Hi, I want to merge two PDF-files retaining the index (the xref-table) of both files. I wanted to write my thesis in LaTeX, except for the covering page which is rather fancy and complex. Now I want to merge the covering page of my thesis with the contents, but I could not accomplish this job with retaining the index using the followi...

MySQL searching long CHAR column, using smaller (substring) CHAR column as index?

So i have a UNIQUE CHAR(255) column, i want to find a particular row, Would it make sense to create CHAR(10) INDEX to make search more efficient? I know a unique is also an index the engine will scan throught the index to where the 1st letter is J, then JO, then JOH but an index of 255 bytes x 1 million records, is a lot of memory "s...

Getting the index number of selected comboBox item

I have a combo box setup with 4 items, with indexes ranging from 0 to 3. Later in my code, I need to do a certain event depending on what is selected. To do this I thought about comparing what the index of the selected combo box item is, because integer comparison is faster than strings, right? Can anyone let me know how I can grab the...

How do you drop a column with an unamed default value constraint and unknown indexes

What is a method of determining if there is a default constraint on a column and its name, and the names of any indexes in order to drop them before dropping the column? ...

.htaccess mod_rewrite has different result on different sites

Hi, I am using mod_rewrite to create SEO URLs. Basically the thing works in the following way: all requests are caught by .htaccess and redirected to a php script (let's say transform.php). transform.php parses SEO URL and transforms it into a normal URL, then includes index.php, then catches application's (index.php's) output and uses ...

jQuery Accordion - Need index of currently selected content part

I have a simple menu on a web page, based on the jQuery Accordion. I'm simplified the code somewhat, but it looks like this; <div id="menu_div" class="nt-tab-outer nt-width-150px"> <h3 class="nt-tab-title"><a href="#">Menu A</a></h3> <div id="menu_1_div"> <a href="itemA1">Item A1</a><br /> <a href="itemA2">Item A2</a><br /> </div> ...

PHP: How do I search in an unindexed array?

I have an array that is formatted like so (this example has 5 keys): [0]: HTTP/1.1 200 OK [1]: Date: Wed, 10 Feb 2010 12:16:24 GMT [2]: Server: Apache/2.2.3 (Red Hat) [3]: X-Powered-By: PHP/5.1.6 [4]: etc.. The array keys sometimes alternate, as one may be omitted. How can I search for the array with "Server: ..." in it, and if it exi...

MySQL Optimization

Hello .. In my database of 5 million records .. Structure of the table: CREATE TABLE IF NOT EXISTS `music` ( `id` int(50) NOT NULL auto_increment, `artistname` varchar(50) NOT NULL, `songname` varchar(50) NOT NULL, `duration` varchar(6) NOT NULL, `url` varchar(255) NOT NULL, `server` int(5) NOT NULL, PRIMARY KEY (`id`), ...

How long should it take to build an index using ALTER TABLE in MySQL?

Hi All, This might be a bit like asking how long a length of string is, but the stats are: Intel dual core 4GB RAM Table with 8million rows, ~ 20 columns, mostly varchars with an auto_increment primary id Query is: ALTER TABLE my_table ADD INDEX my_index (my_column); my_column is varchar(200) Storage is MyISAM Order of magnitude, sh...

mysql composite indeces should i be using them

I got question on index strategies for mysql - mainly when to use a composite index I have a fairly common relational db scenario, heres my table set up: Maintable - table consisting of "products" including brandid, merchantid So I create a table to store the brands and merchants Brandtable - brandname, brandid Merchanttable - merc...

Find the distance between two points in MYSQL. (using the Point Datatype)

Suppose I have a 2 column table like this: | user_id | int(11) | NO | UNI | NULL | | | utm | point | NO | MUL | NULL | | As you can see, it's very simple. utm is a Point data-type. I insert it like this: INSERT INTO mytable(user_id, utm) VALUES(1, PointFromWKB(point(50, 50))); ...

Tweaking the Fill Factor to reduce fragmentation

I have an non-clustered index that has ~1000 pages, 95% fragmentation and a FillFactor of '0'. I rebuilt the index on Sunday. A week later, the index has 95% (or so) fragmentation again. Is this an indication that I need to change the FillFactor to say 80-90%? Should I be tweaking the fill factor each week to see if I can reduce the ...

Tokyo Tyrant indexes

I was wondering about size requirements for Tokyo Tyrant indexes. If I have an index on field x and insert a record with fields y and z only, does the x index grow? Also: does the insert time grow in that case? ...