indexing

Why isn't MySQL using the index for this subquery?

I used to do this: SELECT layerID FROM layers WHERE ownerID = ? AND collectionID = ? Which would give me an array of layerID's, and then I'd loop and do this for each one: SELECT DATA FROM drawings WHERE layerID = ? And it all worked fine. So now I'm trying to do it in one step, so I try this: SELECT DATA , layerID FROM drawings W...

Does mysql index null values?

I have a mysql table where an indexed INT column is going to be 0 for 90% of the rows. If I change those rows to use NULL instead of 0, will they be left out of the index, making the index about 90% smaller? ...

How wrong is it to have a unique and normal index on the same column?

I have the following table structure CREATE TABLE `table` ( `id` int(11) NOT NULL auto_increment, `date_expired` datetime NOT NULL, `user_id` int(11) NOT NULL, `foreign_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `date_expired` (`date_expired`,`user_id`,`foreign_id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEF...

MySQL True vs False optimization

Can someone explain to me why I'm seeing the following behavior: mysql> show index from history_historyentry; +----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name ...

Configure merge_block_size in sphinx search engine

I am powering a web search using Sphinx, and am getting the following error message while building the indexes: WARNING: sort_hits: merge_block_size=76 kb too low, increasing mem_limit may improve performance The problem is I can't find any documentation on where this setting is configured. I'm somewhat versed on Sphinx setup, so I...

using boolean/enum columns in indices

Hi, I`ve read that columns that are chosen for indices should discrimate well among the rows, i.e. index columns should not contain a large number of rows with the same value. This would suggest that booleans or an enum such as gender would be a bad choice for an index. But say I want to find users by gender and in my particular datab...

does mysql index foreign key columns automatically?

The subject says it all... ...

How do you detect if there is an index for a specific column on a table in Oracle?

A table exists that someone else loaded. I need to query against the table, but the lack of indexes makes the query plan abysmal. What I would like to do is detect if there is an index for a particular column, so that I can created it if it does not exist, and not create it if it is already there. Thanks. Evil ...

SQL Server: Floor a date in SQL server, but stay deterministic

(This is related to Floor a date in SQL server.) Does a deterministic expression exist to floor a DATETIME? When I use this as a computed column formula: DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0) the I get an error when I place an index on that column: Cannot create index because the key column 'EffectiveDate' is non-det...

Recommended indexes for query in large table involving a 'date range' and an 'order id'

I have a query (which was created by LINQ to SQL) to get me a list of 'site visits' that were made between a certain date range which resulted in an order (orderid is not null). Theres nothing wrong with the query. I just need advice on creating the correct index for it. I was playing around trying different combinations on a production...

MySQL -- create two indexes, only one appears in SHOW CREATE TABLE output

In have a many-to-many linking table and I'm trying to set up two foreign keys on it. I run these two statements: ALTER TABLE address_list_memberships ADD CONSTRAINT fk_address_list_memberships_address_id FOREIGN KEY index_address_id (address_id) REFERENCES addresses (id); ALTER TABLE address_list_memberships ADD CONSTRAINT fk_address_...

Oracle - Select where field has lowercase characters

Hello, I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name. When rows are inserted into this table, the first name and last name fields are supposed to be in all upper case, but somehow some values in these two fields are mixed case. I want to run a query that will show me al...

SQL Server Hash Indexes

When using the CHECKSUM column type to artificially create a hash index, is the lookup actually O(1) or is it still O(lg n) like it is for a clustered index? I have a table from which I will select based on its ID column and I need the lookup to be as fast as possible, so is the clustered index the fastest possible option? I am looking f...

When should database indexes be rebuilt?

I was reading about refactoring a large slow SQL Query over here, and the current highest response is from Mitch Wheat, who wants to make sure the query uses indexes for the major selects, and mentions: First thing I would do is check to make sure there is an active index maintenance job being run periodically. If not, get all existi...

MySql Index Fill Factor?

How do you specify the Fill Factor when creating an index in MySql? ...

MySQL - how to use index in WHERE x IN (<subquery>)

I'm using this query to get all employees of {clients with name starting with lowercase "a"}: SELECT * FROM employees WHERE client_id IN (SELECT id FROM clients WHERE name LIKE 'a%') Column employees.client_id is an int, with INDEX client_id (index_id). The subquery should IMHO return a list of id-s, which is then used in the WHERE...

Two column primary key in MySQL

I have a very simple problem and a solution that will work, but I'm looking for a simpler one. I'd like to prevent rows from being added to a database when multiple values equal existing values. For example, if a2=a1 AND b2=b1 then the data gets rejected. If only a2=a1 or only b2=b1 it is allowed. Basically I want it to act like a pr...

SQL Server Clustered Index - Order of Index Question

I have a table like so: keyA keyB data keyA and keyB together are unique, are the primary key of my table and make up a clustered index. There are 5 possible values of keyB but an unlimited number of possible values of keyA,. keyB generally increments. For example, the following data can be ordered in 2 ways depending on which key c...

Do i need one or two Unique Key Index's for this table?

Hi folks i'm trying to create a Users table that only has OpenId registrations, exactly like StackOverflow. I'm storing in the table OpenId Identifier (their login name) Alias (which is the display name to show to the public) Some other openId stuff So .. I want make sure that there is only ONE user in the system that has the open ...

One 400GB table, One query - Need Tuning Ideas (SQL2005)

Hi, I have a single large table which I would like to optimize. I'm using MS-SQL 2005 server. I'll try to describe how it is used and if anyone has any suggestions I would appreciate it very much. The table is about 400GB, has 100 million rows and 1 million rows are inserted each day. The table has 8 columns, 1 data col and 7 columns us...