Tag Info

New answers tagged

0

I would go with detailed if it finishes in a timely basis. I run a check index statistics script every night and if it can rebuild within the time allocated for low activity I would go with detailed because the resulting statistics seem to produce better query plans.


3

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use: ALTER TABLE table_name MOVE ...


0

You can rebuild the indexes after or use the WITH UPDATE INDEX when executing your statement. E.g. ALTER TABLE table_name MOVE COMPRESS FOR ALL OPERATIONS WITH UPDATE INDEX; Note. Your statement will not compress existing data: only future data inserted.


1

No, a basic btree index on a small column is typically cheap to maintain. Of course it gets slightly more expensive when the table has accumulated some bloat from dead rows, but the difference should be small. And you have to consider additional storage on disk for the index. One thing seems worth mentioning: Updates on columns involved in an index in any ...


3

Unless an index is filtered, they should all be written to the same number of times under normal DML activity. If a row is updated or added, any columns in any index must also be added/updated. So you should not expect some indexes to be written to a different number of times, unless you are reorganizing or rebuilding individual indexes specifically. ...


4

I read somewhere that it's not relevant to have more than 10 indexes No hard-and-fast rules like that are generally applicable. 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. The key problems with too many indexes which you need to ...


0

Your key distribution is probably very lopsided. Here is how you can tell: SELECT COUNT(1) FullTableCount FROM mytable; SELECT COUNT(1) MyKeyCount FROM mytable WHERE col_one = 'a' AND col_two = 1 AND col_three = 1; If MyKeyCount > (FullTableCount * 0.05), the Query Optimizer will not choose the index with columns col_one, col_two, col_three. Therefore, ...


0

Multiple aspects needs to be considered when creating an index. An index on columns A,B is not the same as an index on B,A. the performance of a query which filters on both column A and B will be different depending on the order of the columns in the index. Indexes slow down DML operations and increase the size of the database. You can refer to the ...


3

You have a time series (measurements) organized by id (clustered index). I am yet to see a single case where using id as clustered key for time series makes sense. All queries will ask for date ranges. Organize by time: CREATE TABLE measurements ( id bigint IDENTITY, parameter_id int NOT NULL, measuretime datetime NOT NULL, value float NOT ...


1

I think the optimiser is right. When you use INCLUDE, it only stores the included column values on the leaf level of the index, they do not make up the key. So what it is suggesting is that it can decide which branches of the index to scan (measuretime is the key, so it leaves a huge chunk of records out), which means the WHERE doesn't need to test each row. ...


3

Use this one: DROP INDEX SYSTEM."IX_isbn_send_h.D_STAT"; Note, when you use quotes, then names are case-sensitive in Oracle.


1

Well, I spent a while looking at writing a custom postgres C extension, and wound up just writting a Cython database wrapper that maintains a BK-tree structure in memory. Basically, it maintains a in-memory copy of the phash values from the database, and all updates to the database are replayed into the BK-tree. It's all up on github here. It also has a ...


4

MySQL's optimizer looks only at what indexes are available. There is an exception in 5.6: If you have a subqueries such as FROM ( SELECT ... ) JOIN ( SELECT ... ) ..., there are no indexes on the temporary tables that are created. This used to lead to terrible performance. Now, the optimizer will try out various indexes, and create the best one for the ...


2

MySQL has no such feature. It is left to the user to "normalize" the data either for avoiding having to update multiple spots and/or for saving space. In your example, it is generally not practical to do such with first/last name. But it may be advisable for "locations". It is not practical to dedup names (for example) because the payoff is poor. With ...


1

If you could change the WHERE clause for the sake of the benchmark, do one of two things: OPTION #1 : Table Scan SET PROFILING=1; SELECT SQL_NO_CACHE field_a, field_b FROM table; SELECT SQL_NO_CACHE field_a, field_b FROM table; SELECT SQL_NO_CACHE field_a, field_b FROM table; SHOW PROFILES; OPTION #2 : Change WHERE xxx to WHERE 1=1 SET PROFILING=1; ...


0

... FROM tbl IGNORE INDEX (idx1, idx2, ...) ... Unfortunately, you need to know the names of all the indexes.


0

When should I rebuild indexes? When the index fragmentation percentage is more than 30%. Is there a case for rebuilding indexes on a regular basis? There is no such case, but in general, doing Index Maintenance once in a week, over the weekend is the best practice to keep the environment stable. I would recommend using maintenance scripts from ...


0

In addition to above answers, B-tree, cluster and fulltext indexes can be multiple and will work from left-to-right. For example in: where `1` like '%1%' and `2` like '%2%' and `3` like '%3%' You should have multiple fulltext index on (1,2,3) to achieve good performance. If you have only one column in where condition, fulltext index on that column will ...


0

First of all @Falcon explained correctly that the Index can't work with like if there is a % at the beginning. But with the normal FULLTEXT IN NATURAL MODE it's only possible to search full words and the words have to be at least 4 chars long (if you have access to the database you can change this) You might want to have a look at BOOLEAN MODE option in ...


1

Let me help you to understand how a "standard" index works. Most databases indexes are just B-Trees (not to confuse with a binary tree). Simply speaking, when you query an indexed column, a binary search will be performed. A binary search performs generally in O(log(n)) and thus you can find individual rows quite fast, even if there are many of them. The ...


1

Since the selective predicate on the big table eav_value_text_data big table is v.value = 'rs145368920', you need an index on value more than anything else. The index on attribute is hardly relevant - only in combination with the first to allow index-only scans if possible: CREATE INDEX eav_value_text_data_val_att_idx ON eav_value_text_data (value, ...


2

If I were setting up a new server, I wouldn't use anything below 9.3.6. There have been many improvements since 9.1. And if you use 9.4.1, there is some code to let it use larger amounts of RAM for index creation than it previously could. Neither of the parameters you listed are particularly important for creating indexes. The most critical for that ...


2

The objects that have definitions found in the sys.sql_modules Catalog View are the only ones that have full definitions, and indexes are not in this group. The sys.check_constraints and sys.default_constraints Catalog Views have partial definitions, but again, those aren't indexes. So no, you are not going to find the text definition of indexes. What you ...


0

In the case of updates, I believe the updated column(s) would need to be involved in the Indexes before the Update would be slowed by indexes. I don't have direct testing on this theory, but it makes sense to me.


2

The first one is a unique constraint. It can be added to an existing table with: ALTER TABLE ADD CONSTRAINT ... Details in the manual here. It is implemented using a unique index. Per documentation: Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint. A uniqueness ...


0

Absolutely NO! Creating index on tables decreases insertion performance and you must create an index only if you really need that (if you include the column in select queries many times).


4

Would you find it useful to have the phone book, only having it as a list of Surnames with phone numbers, plus a list of Firstnames with phone numbers, and a list of towns with phone numbers? Because essentially, that's what you're getting if you index each column individually.


2

If we are talking about a currently supported version of Microsoft SQL Server, a non-clustered index does indeed contain a copy of the clustered index key. This is one reason that there is emphasis on keeping keys as narrow as practical. Obviously a clustered key consisting of an integer would take much less space in the non-clustered index and therefore ...


1

Before start, please note that external keys are usually bad as primary keys, because they are larger than needed, they can change and some people may not have one, or have duplicated ones (even if legally that shouldn't happen). Speaking correctly, the EIN is a code of 9 digits. As such, the technically correct value should be a string. In particular, for ...


2

Simplified query to make it readable: SELECT * FROM "Follows" f JOIN "Users" u ON f."followeeId" = u."id" WHERE f."followerId" = 169368 ORDER BY f."createdAt" DESC LIMIT 1000; Your index follows_followinglist_followerid_createdat_idx looks good for the job. In Postgres 9.2+ it might get a bit faster if you append "followeeId" to the index - if ...


4

I would change your table layout. If you used something like: (LineId int, BallSequence tinyint, BallVal int) The combination of (LineId, Ballsequence) would be your PK. Then your query would be: SELECT COUNT(*) FROM ( SELECT 1 FROM dbo.Lines GROUP BY LineID HAVING SUM( CASE WHEN BallVal IN (@ball1, @ball2, @ball3, @ball4, @ball5, ...


1

If the table doesn't have many inserts or updates, then it's basically just wasted space (+ maybe longer maintenance / backup time) But for every insert and update in case of the indexed fields, then each of the indexes must be maintained, and that's then slowing down the operations. At least I would recommend looking first at the index usage statistics ...


3

Probably some of those indexes are used, so deleting them all is not a good idea. You can review index usage stats and find bad NC indexes with this query (taken from Glenn Berry's Diagnostic Queries): -- Possible Bad NC Indexes (writes > reads) (Query 47) (Bad NC Indexes) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], ...



Top 50 recent answers are included