Tag Info

New answers tagged

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

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 ...


1

Ideas to get you started: This is an excellent idea, and the one I'd go for first. Except, SQL Server 2000 does not support INCLUDEd columns in indexes. Clustered/non-clustered indexes is a different discussion entirely - to put it simply, a clustered index is actually the physical storage "order" of the data (the index is the data), while a non-clustered ...


3

The CTE is not needed here and poses as optimization barrier. A plain subquery generally performs better: SELECT * FROM ( SELECT id ,rank() OVER w AS global_rank ,lag(slug) OVER w AS previous_slug ,lead(slug) OVER w AS next_slug FROM entries WHERE competition_id = 'bdd94eee-25a4-481f-b7b5-37aaed953c6b' ...


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

Two more things in addition to what has been said already: IN with long lists does not scale well (at least it did not in my tests on Postgres 9.1; have to run new tests ..). I found it to be faster to prepare a derived table and JOIN to it. Details in this related answer on SO. If laps is a big table and your query only select a small fraction of rows ...


4

You could try simplifying and using LATERAL for joining the laps table: SELECT races.*, tmptimers.last_start_time, tmplaps. last_updated_at FROM races LEFT JOIN timers AS tmptimers ON tmptimers.user_id = 1 AND tmptimers.race_id = races.id LEFT JOIN LATERAL ( SELECT updated_at AS last_updated_at FROM laps WHERE ...


6

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values. The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid ...


2

To use a filtered index it wants to see the predicate there, or one that matches very closely. So explicitly saying AND ins.ParentID IS NULL is going to be useful. Now, you should generally include the columns you're filtering on in the index itself because of a QO quirk. If the predicate in the query matches exactly the one in the index, and you're not ...


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) ...



Top 50 recent answers are included