Tag Info

New answers tagged

2

You could try MariaDB which supports virtual columns. Indexes are partially supported. Virtual columns do not support primary keys and indexes can only be based on PERSISTENT virtual columns. PERSISTENT columns can be part of a foreign key and can be referenced by foreign keys, but ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL are ...


1

See this post, which links to a big query summarizing all the indexes which may not be pulling their weight.


1

You can monitor the index usage through pg_stat_user_indexes and pg_statio_user_indexes More details about the statistics collector can be found in the manual: http://www.postgresql.org/docs/current/static/monitoring-stats.html You should be careful with dropping unused unique indexes though. They might not be used for reading, but they are most probably ...


1

With only 5 values the index isn't honing to be very selective for joining so it'll pretty much always index scan unless looking for just one value. That query plan/anal output suggests that it thinks half the work is in matching the rows there with the CTE, could you try run the group+aggregate over the whole lot. If it is index scanning anyway you might ...


3

Going out on a limb here (basic information is missing), partial indexes will probably be your best bet. Much easier to handle than partitioning the whole table, it offers similar performance for the split case and allows much better performance for queries on the whole table: CREATE INDEX tbl_nodelay_idx ON tbl (tbl_id, ??) WHERE delay <= 0; CREATE ...


0

Logging is always a good use case for non relational stores (text book example). You've only given us one query pattern, so going by that I would create a single compound index, equality to inequality. I would suggest reading up on indexes at: MongoDB Index Introduction and MongoDB FAQ: Indexes


0

Given the information in your comments there's not much you can do index-wise beside the index that you proposed. This index will have poor selectivity. Dependent of the size of the table and the update frequency of delay you might consider range partitioning, one partition for delay > 0 and one partition for delay <= 0. Instead of scanning the index and ...


7

Under the covers sp_indexoption just does an ALTER INDEX as well. My recommendation would be to forget about sp_indexoption and just utilize ALTER INDEX: alter index IX_YourIndex on dbo.YourTable set ( allow_row_locks = on ); go As noted in the BOL reference for ALTER INDEX, when you specify just the set options: Specifies index options without ...


4

Ok, so the documentation is not exactly stellar, but you must think this shouldn't work online because the doc says: Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Which - if you invert it - states that an ...


0

I think I've found a workaround, though I'm going to file a bug report unless someone can explain why this works. The problem appears to originate with the id portion of the order by clause. When using either order by name asc, name asc, id asc or order by name asc, id asc, id asc then the query uses the existing ...


0

The extra predicate wont mess up the indexing. Most likely your index will be evaluated first and any rows that are found will be evaluated against the predicate cityid = 100. On the other hand, assuming your index is defined as (usergroupid, birthdate). If you remove usergroupid = 54 from your query, the index won't do any good and you will have to ...


2

It depends on the method you're using to alter the column. We'll start with a simple table: CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NVARCHAR(200)); CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName); Then use the SQL Server Management table designer to change the CustomerName column to a VARCHAR(200) ...


4

Since SQL Server can skip NULL rows to start the range scan, the cost of either index is identical, so this is basically a coin toss for the optimizer. Look at the plans in SQL Sentry Plan Explorer* by default and when you hint the index (click to enlarge): Since it's a toss-up, I don't know what benefit you'd get out of forcing SQL Server to ...


2

I would rewrite the query as following SELECT DISTINCT id FROM ( SELECT id from patient WHERE company_id=1 AND name_last LIKE 'peter%' UNION SELECT id from patient WHERE company_id=1 AND name_first LIKE 'peter%' UNION SELECT id from patient WHERE company_id=1 AND name_remote LIKE 'peter%' ) A; I would also change the indexes as follows ALTER ...


2

If a significant portion of the rows match company_id in (1), then MySQL will choose to do a table-scan instead of using the index. In my experience, "significant portion" is about 20%. Think of it this way: in the index at the back of a book, why don't they index words like "the"? Because the index entry would just show a list of every single page number. ...


3

No. To my knowledge, changing a column that is included in an index is not possible (you'll get an error message to that effect). You would have to drop the index before changing the column, and then re-apply the index again. The only exception to this that I am aware of is that you can change a column from NOT NULL to NULL without having to drop the index ...


0

Of course, the index size collapsed because you reduced the PRIMARY KEY to a single column and removed KEYid_str(id_str),. This definitely reduced all secondary indexes. That is excellent. What about the removal of columns ? You removed geo, which were all NULLs. There was no shrinkage for the data. Why ? Since all geo columns were NULL, it took no space ...


0

Do you want to perform the update once or on a regular basis? If the update on million rows is done once, then the best solution is to create a temp column "processed" on #testing2 table of bit (int,tinyint) which will serve as your null filter. The index on bit or int columns works a lot more optimal than on varchar. Also, having 2 type of values on index ...


1

It is not doing an extra bitmap scan. It is doing a bitmap scan instead of the regular index scan. Why is it doing the bitmap scan instead? Because it thinks it will be faster that way. Without the LIMIT and the ORDER BY, it anticipates that using the bitmap scan will let it do the IO on the table heap in a more efficient manner. You can see if ...


1

I will elaborate on the @Jeffrey Kemp's comment. Since the information about authors of modifications is not collected in the objects' metadata, you will want to use database auditing mechanisms. As per Verifying Security Access with Auditing chapter of Database Security Guide, there are several types of auditing, but Standard Auditing is probably the ...


1

Index definition Your multicolumn index generally looks good. If all or most of your queries use order by score desc, id desc, it would be a bit more efficient to define it with matching sort order and a simpler condition: CREATE INDEX prod_category_score_id ON products(category, score DESC, id DESC) WHERE active; WHERE active = TRUE is just a more ...


0

Below this section it goes on to update, in the exact same manner, every table in the view. I cannot see why this is, as I have specified the Table_Year (which the table is partitioned on) within the query text. Shouldn't SQL only update the necessary table? The view meets all the partitioning requirements for both Table_Year and calStartDate. The ...


1

[Note: also answered at answers.SQLPerformance.com.] These aren't actually partitioned tables, and even if they were, partition elimination wouldn't really work for updating indexes unless all indexes were also partition-aligned. Since you are using Express Edition and can't actually use partitioning, I have a different approach to recommend: dynamic ...


1

Here you have a video on the basic inner structure and inner workings of indexes. I recommend you to watch it all. Basically, indexes are ordered structures on disk (although they can be cached, and they normally will for better performance) that will allow certain operations to be done faster. In particular, in MySQL, B-tree/B+tree (the most common ones) ...


0

Set the DB Recovery mode to Simple (to avoid growing the Log file too large This is not totally correct. Simple recovery is almost same as full recovery just the log truncations are taken care by Database engine in case of simple recovery. In simple recovery after transaction commits or log file grows 70 % of its size log truncation occurs while in full ...


1

I know that reindexing the PK will lock the table, so I was wondering what best practice for this operation is? Well having some DB file size/growth and free disk space monitoring would be good so this situation never happens. As you are in this situation I think the plan you have outlined is your only choice until you get more disk space and you really ...


4

You may be able to reduce index rebuild/reorg times by moving to NEWSEQUENTIALID(). You are currently inserting around 4,500 rows into the table per day. The table has around 6.3 million rows at present. That's around 0.7% of the table per day. Assuming every insert on the table results in an index-page split, this will result in 9,000 pages per day ...


-3

"…is there any benefit to changing the GUID generation to sequential using newsequentialid()?" No. Sequential GUIDs are only appropriate when there is a clustered index on the GUID column and you want to avoid page splits caused by inserts. Edit to address the comment below: All nonclustered indexes suffer from page splits when data is inserted. For ...


0

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D). --search any "field" for quick: select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true --search B "field" for quick: select 'quick:1A brown:2B quick:3C'::tsvector @@ ...


1

VACUUM ANALYZE makes the difference in your example. Plus, as @jjanes supplied, the additional statistics for the functional index. Per documentation: pg_statistic also stores statistical data about the values of index expressions. These are described as if they were actual data columns; in particular, starelid references the index. No entry is made ...


4

When you create an expression index, it causes PostgreSQL to gather statistics on the that expression. With those statistics on hand, it now has an accurate estimate for the number of aggregated rows that the query will return, which leads it to make a better plan choice. Specifically in this case, without those extra statistics it thought the hash table ...



Top 50 recent answers are included