Tag Info

Hot answers tagged

10

You're basically describing a B-tree index and a hash index. They both have a place, but both are best suited for different jobs. Advantages and disadvantages B-tree (and B+-tree) indexes are usually balanced. This means that looking for a value will always take the same amount of time no matter where in the tree it falls (O(log n)). Generally, the ...


9

...why with clustered index, the deadlock is still there (though hit rate seems to be dropped) The question isn't precisely clear (e.g. how many updates and to which id values are in each transaction) but one obvious deadlock scenario arises with multiple single-row updates within a single transaction, where there is an overlap of [id] values, and the ...


9

If I saw a data model where a table had 2.1 million rows, one of the columns had only 5 distinct values, and I knew that people wanted to get a listing of those distinct values on a somewhat regular basis, I would strongly suspect that the data model was missing a lookup/ dimension table. Rather than trying to tune your current query, I would bet that the ...


6

If Oracle only scans the index it cannot decide if there is a row with NULL in this column. So it makes a full table scan. A possible workaround: create a bitmap index on column4. This index contains the NULL columns, too. More details can be found in this post Why isn't oracle using an index for distinct query ?


6

I'd first add an index on (project_id, user_id) and then in 9.3 version, try this query: SELECT u.user_id, c.number_of_nodes FROM users AS u , LATERAL ( SELECT COUNT(*) AS number_of_nodes FROM treenode AS t WHERE t.project_id = 1 AND t.user_id = u.user_id ) c -- WHERE c.number_of_nodes > 0 ; -- you probably want ...


5

GETDATE() is not guaranteed to be unique, no. Especially if it is a datetime, where milliseconds are rounded up or down, and of course when not all data is coming from the same user, you are almost guaranteed to have collisions. Of course, a clustered index does not need to be unique, since SQL Server will make it so if it isn't (but only when it needs to). ...


5

SMS suggested adding a covered index to one of my tables that indexes on the column I believe makes perfect sense. However, it also recommended including (covering) every other column in the table. The suggestions shown in SSMS are actually generated by the query optimizer while it searches for an efficient physical execution plan for the query. The ...


4

Main problem is the missing index. But there is more. SELECT user_id, count(*) AS ct FROM treenode WHERE project_id = 1 GROUP BY user_id; You have many bigint columns. Probably overkill. Typically, integer is more than enough for columns like project_id and user_id. This would also help the next item. While optimizing the table definition, consider ...


3

I have something similar with using SELECT IF() statement in MySQL if you are trying not to have procedures. select if ( exists( select distinct index_name from information_schema.statistics where table_schema = 'schema_db_name' and table_name = 'tab_name' and index_name like 'index_1' ) ,'select ''index index_1 ...


3

The query is slow because first, this query has an OR condition which cannot be optimized with a single index (while conditions with only AND can) and second, because besides the emails, it has to check the isActive=1 condition. So it prefers to do a table scan. The other possible path would be to use both email indexes with the Index Merge Union Access ...


3

Index creation is done within a transaction. If you interrupt it, SQL Server will rollback the transaction. This might take a while, but it's harmless. Of course, there are locks held while the transaction is active so this can impact your server's performance. If you lose power in the middle, SQL Server will automatically rollback the partially ...


3

Cardinality Even with high cardinality, the tipping point used by the MySQL Query Optimizer is either the key distribution or the storage engine. Back on November 13, 2012, I discussed how lopsided keys can make the Query Optimizer choose different indexes (sometime not choose and index at all) : Must an index cover all selected columns for it to be used ...


2

Yes, what you're talking about is a Filtered Index. It works just like you think, by creating an index off of a subset of data. Below is an excerpt from the above reference: A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a ...


2

What are the adventages and disadventages of both solutions? The second solution can not perform range scans. It is great for selecting a single ID. But what if you want ids 3 through 8? It has to grab all the records individual which in the real world isn't just O(1) * 6 records to retrieve. In a large, production database with a HashMap index you ...


2

What are the adventages and disadventages of both solutions? The second solution can not perform range scans. It is great for selecting a single ID. But what if you want ids 3 through 8? It has to grab all the records individual which in the real world isn't just O(1) * 6 records to retrieve. In a large, production database with a HashMap index you would get ...


2

The comment from @ChristianAmmer is the key to answering your question. Why ??? First look the the WHERE clause DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds ...


1

I will answer each the the three questions QUESTION #1 Since I'll probably move to mysql cluster, does it make sense to switch from InnoDB to NDB engine and use HASH indexes on "category" and "status" columns? ANSWER TO QUESTION #1 Hash indexes are for one-to-one lookups. Hash indexes are only available for the MEMORY Storage Engine (See my May 17, ...


1

Your query can be simplified to: SELECT COUNT(DISTINCT t.id) FROM cs AS t JOIN uploads AS u ON t.upload_id = u.id WHERE t.user_id = 1 AND t.upload_id IN (1,2,3) AND u.term > '' AND t.amount > 0 ; and also: SELECT COUNT(*) FROM cs AS t WHERE t.user_id = 1 AND t.upload_id IN (1,2,3) AND t.amount > 0 AND EXISTS ( SELECT ...


1

I created your scenario with COLLATE = "C", and both queries use a bitmap index scan index on other_names_lower_trgm_gin as expected. SQL Fiddle with a table of ~ 10k rows, Postgres 9.2.4, COLLATE = "C". There is probably something wrong in your setup that is not in your question. Run (takes some time for big tables and an exclusive lock!): VACUUM FULL ...


1

These two queries may help you. The first will list all of the tables and indexes on those tables in your database. If the table does not appear in the list is does not have any indexes defined on it. These queries assume SQL Server version 2005 or newer. SELECT I.name AS IndexName , T.name AS TableName , I.is_primary_key AS IsPrimaryKey ...


1

I am not sure if you're interested in all constraints but INFORMATION_SCHEMA.TABLE_CONSTRAINTS doesn't seem to return the DEFAULT constraints -- TABLE_CONSTRAINTS (Transact-SQL) CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY This query will do a simple count against the sys.objects DMV: select COUNT(*) from sys.objects o where o.type_desc like ...


1

No, something is incorrect. The check on sys.indexes should return a row even if your table has no indexes. The heap still has a record in sys.indexes with a type_desc of 'HEAP' and type of 0. I think you probably need to make sure you are in the right database context since OBJECT_ID() and sys.objects are database-specific. Try this: USE MyDatabase ...


1

If you execute frequently those kind of queries: SELECT C1 FROM tbl WHERE source = 'x' and state = 'y' Then it might be advisable to build an additional index on both source and state. Maybe this question Should I create an index for non key columns? might help you to understand this concept.


1

You just need to add a B-tree index to the some_date DB field. Partial indexes work, only if you know your query parameters and want to exclude / include particular ranges (http://www.postgresql.org/docs/current/static/indexes-partial.html). Another alternative is to use table partitioning ...


1

No, you can't. An index, in MySQL, will either be a BTREE single or multi-column index. a HASH index (available only for MEMORY tables), that can span several columns. a SPATIAL index (available only for MyISAM tables). a FULLTEXT index (available for MyISAM and 5.6.4+ InnoDB tables), that can span several columns of CHAR, VARCHAR or TEXT type. You ...


1

There is some space used for the upper levels of the B-tree (how much depends on how large the table is). However if you look at a Clustered index and a Non Clustered index of the same data (exact same keys and including all columns of the table in the non clustered index) the upper levels of the B-tree would look very similar (if not the same). The ...


1

The quick answer is yes. Indexes in MongoDB mostly follow the same logic for usage and creation as you would do in MySQL. Nevertheless, as the two databases are different (MongoDB is document based, not relational, etc) there are some aspects you might want to consider (for instance, there are no joins - your data model/organization needs to reflect this ...


1

You can use a tool like pt-online-schema-change to add the index without blocking your application. The basic logic is as follows: create empty copy of the table with the new index add triggers to the existing table so that any changes that hit the table will be applied to the copy of the table* start copying the rows from the old table to the new once the ...



Only top voted, non community-wiki answers of a minimum length are eligible