Search Results

If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index (and comment the source) rather … Also a specific index can be named in an index hint (i.e. …

Index Scans with Row Lookups: With no index that can be directly used for a seek is found but an index containing the right columns is present an index scan may be used. … Index Seeks (with or without row lookups): In a seek not all of the index is considered. …

You are right in that your example query would not use that index. … If you do define [customerId], [dateCreated] as an index, for example, remember that the query planner will be able to use that for queries that would use an index on just [customerId] if present. …

on disk is a very heavy operation (and IIRC mySQL doesn't support live index rebuilds so holds a table lock during the operation). … more efficient for large strings (where computing the hash value and searching the tree index by this value is always likely to be faster then just searching a tree index using the larger values for comparison …

Index creation is essentially a sort operation, so is at best has a growth complexity of the order n log n on average (you might find it does better in some cases, and is not likely to do much worse). … If all your relevant data pages fit into RAM and are already in RAM, and the index will fit also, and your DBMS does not force index pages to be written before the creation is complete (so index blocks …

to use a combined index if present. … If you only have an index on each individually but no combined index the DB will either search one or the other index and separately filter the results with the second clause, or scan both and marry the …

There will be no difference for read operations, once written the entries in the index will be the same. … Only create a unique index for values that you know should be unique. …

A "kitchen sink" query like that (multiple distinct filtering clauses one or more of which is used depending on the value of an input) is never going to be sargable even if all its individual clauses …

There will be a very very small difference in parsing time for statements that specifically mention the index or generating output that mention the index, but this is so vanishingly small compared to all … When the index name is not explicitly used then even that difference is gone: the index will be looked up by the columns it contains data for not by its name. …

I asume by "recalculating" you are referring to completely rebuilding an index. … You can reduce the affect of index rebuilds if you have Enterprise Edition by using online index builds - there will still be a time were the table is locked as the newly generated index is attached to …

Now, tables have max 5k rows and in future they may go maximum up to 100k rows In cases like this, where time and resource permits, the best way to check this is to manufacture realistic looking …

The amount of work to maintain the balance of the tree structures for each index is fairly high and depending on insert order can result in both significant index fragmentation (i.e. the location of pages … Also if there is existing data dropping index and constraints will be a problem if there are user actively using the system(s) the database backs. …

Essentially you would be manually partitioning the table (and appying the index to only one of the partitions). … You could maintain an extra column with an index and only have a value in that column when the condition you want the index to be based upon is true, but this is likely to be labour intensive and of limited …

An index on region may help, but not a lot unless that column is quite selective (or at all if the majority of the rows are from the region you are looking at). … With that change in place, a combined index on both timestamp and region is likely to be most helpful here. …

If you have more than ten columns (or combinations of columns as an index can cover several) that are regularly searched by then you probably need more than 10 indexes. …