New answers tagged

1

I very much doubt it can do anything with an index starting with prices.pricegroup_id because the where relevant clause covers multiple distinct values. An index on currency_id is liable to be useful for filtering, currency_id, pricegroup_id might perform better because it can read that value for the other WHERE clause to filter out the rows before needing ...


1

You'll want an index with all three columns in your Key Values: CREATE NONCLUSTERED INDEX IX_*YourNamingSchemeandTableHere* ON dbo.*YourTable* (col1, col2, col3) --Key Values INCLUDE (*Any columns commonly selected here*); --Included Columns GO The order of the columns 1-3 is up to you, based on what is most important to your system. SQL Server generates ...


2

A composite index with any order of col1,col2,col3 would work here. The query you have shown in the question would be able to perform three seeks into it for the three or-ed predicates. To determine the best order out of the 6 possible permutations I would first look at other queries, or alternatively you could look at the missing index DMVs. Quite likely ...


3

For this WHERE clause, I would try an index on (customer_account_id, sync_inactive, last_modified, id): ALTER TABLE main_contactinfo ADD INDEX customer_active_last_modified ( customer_account_id, sync_inactive, last_modified, id ) ; The ORDER BY with e small LIMIT complicates things though, so a different order of the columns in the ...


2

Don't randomly add indexes. Look at your queries to decide which indexes are needed. See my cookbook. InnoDB really needs a PRIMARY KEY. Keep in mind that a PK is, an index, is UNIQUE, and is clustered. So don't add any index(es) that start with the same column(s). WHERE a=2 AND b=4 begs for a "composite" index: INDEX(a,b) or INDEX(b,a). Those are ...


2

For a table with 1000 rows I think it does not make sense to put any index. The complete table could fit into memory and that would be fast enough. But if you want to put indexes you should always take the business value into consideration. Take into account that each index generates an overhead to the table. Not sure where the limit is, but in any case ...


1

It turns out there was a trigger on the samen table that also updates it, that forces an update for the index used. It works near instant with the trigger disabled.



Top 50 recent answers are included