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...
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? ...
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...
Can someone explain to me why I'm seeing the following behavior: mysql> show index from history_historyentry; +----------------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name ...
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...
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...
The subject says it all... ...
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 ...
(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...
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...
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_...
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...
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...
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...
How do you specify the Fill Factor when creating an index in MySql? ...
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...
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...
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...
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 ...
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...