Tag Info

New answers tagged

0

Aha! I've found the answer. Talk about an edge case. First, I found this post from 2007, where someone says: ...the AFTER trigger for synchronizing the CTXCAT index on [column] is not firing (since my update statement does not include the indexed column). ... Oracle, would it not be better to generate the CTXCAT trigger to examine the ...


1

In order for the table to be partitioned, your clustered index should be created as:- ALTER TABLE [dbo].[Logs] ADD CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ( [logId] ASC, [time] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ...


2

It is not possible to have an INCLUDE column for an index that enforces a constraint. This has been brought up, but marked as "won't fix". If the benefits of having a covering index are that great, consider revising the indexing strategy that you currently have on that table to maximize query performance.


1

How do we add an INCLUDE in an index which is automatically created (through CONSTRAINTS)? Answer: INCLUDE (column [ ,... n ] ) represents non key column, Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and non-key columns. Check Create Index Arguments--> Include and Create Indexes with Included Columns ...


5

In SQL Server, for a regular index (i.e. not columnstore or Hekaton) the storage engine can choose one of two methods to find the start or end point of a search. The first option is a binary search using the offset array at the end of the page. The second option is to use linear interpolation. After the search start/end point is found, any range-scan ...


1

You cannot rely on DISABLE KEYS; and ENABLE KEYS; for InnoDB because it is not implemented in the InnoDB Storage Engine. Running ALTER TABLE ... DISABLE KEYS; and ALTER TABLE ... ENABLE KEYS; were designed for MyISAM. As it says in the MySQL Documentation for ALTER TABLE: If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a ...


1

Your total desired index size for (className, key_, owner) is: (75*3 + 1 + 1) + (200*3 + 2 + 1) + (255*3 + 2 + 1) = 1598 bytes InnoDB allows indexes up to 3500 bytes if each column only indexes up to 767 bytes. MyISAM only allows 1000 characters in total (changing the block size). Probably your production database is using MyISAM instead of InnoDB. Maybe ...


2

I suspect the difference is a Unicode length issue. As you are not using a single-byte-per-character collation the total length of the fields in your key is 530 character (75+200+255) and not 530 bytes, and 530 characters could easily be more than 767 bytes depending on what language you are storing in those strings (in fact definitely will be if mysql ...


3

Your first column in the index is cl_totalcrawltimes. In the WHERE clause you're using this column to define a lower range (cl_totalcrawltimes > 0). Depending on the data distribution this most probably will lead to an index scan instead of an index seek. Furthermore, cl_crawlsource is not part of the index (the include section, preferrably), so this would ...


1

You have a big problem for one reason: BTree indexes (which are the only format available for InnoDB) are highly inefficient for filtering on more than 1 range. There is one thing that you must understand: in general, using more than 1 index per table access is not possible/efficient (there are some cases where union_merge is faster, but that is an ...


0

I don't be believe there are currently any optimization differences in the area of index maintenance in this regard. In addition to network overhead, the other advantages of the multi-value statement are in the parsing, locking, etc. (Even though the table lock on tbl is taken by the first insert statement and then maintained throughout the rest of the ...


0

Having tried different orders of doing things it does seem that performing a VACUUM before a REINDEX instruction is the only way to get the reduction in size, perhaps because the un-vacuumed space adds to the index (indexing of deleted records?). Forcing a table rewrite by using ALTER TABLE blah ALTER COLUMN whiffle SET DATA TYPE whiffle_type; does the ...


1

The index should work as is. As pointed out by @jjanes, you just needed to run ANALYZE. However, I suggest you modify the index. The expression in your definition is not useful. CREATE INDEX foo_idx ON intentions ((data LIKE '%foo%')) WHERE data NOT LIKE '%foo%'; The expression is always FALSE and useless noise. Either simplify to the slightly cheaper ...


1

I needed to run ANALYZE for the proper query plan to take effect. Once this was done, everything worked like a charm. My final index looks like this: CREATE INDEX foo_idx ON intentions ((data LIKE '%foo%')) WHERE data NOT LIKE '%foo%';


1

The NOT LIKE will be problematic. Imagine this query without the NOT. The index could be walked to find the interval of values (indexes are sorted lists) which match. The NOT means you want everything else, both before and after the matching interval. It's a pretty special optimiser which will do that. You may be able to invert the indexes to identify ...


1

You could try a slightly different approach. CREATE INDEX foo_idx ON mytable (strpos(name,'foo')); SELECT * from mytable where strpos(name,'foo') = 0; Index on Expression


1

Setting ft_min_word_len only affects MyISAM. You need to set innodb_ft_min_token_size to 1 since the default is 3. Once you set innodb_ft_min_token_size to 1, go back and do ALTER TABLE addresses DROP INDEX address_index CREATE FULLTEXT INDEX address_index ON addresses(street); Give it a Try !!!


4

Query Your query is forced to scan the whole table (or the whole index). Every row could be another distinct unit. The only way to substantially shorten the process would be a separate table with all available units - which would help as long as there are substantially fewer units than entries in all_units. Since you have ~ 11k units (added in comment) for ...


1

I discovered that pgAdmin III does this by default. When you create a new foreign key constraint using the GUI, there is a checkbox "Auto FK Index" on the Definition tab. I hadn't noticed this until just now so it explains why the index was being created.


1

No is the short answer, and you don't really need this. Full-text indexes are inverted indexes so they store the split words by the unique doc_id that you have to specify when you create the full-text index. This must be a "unique, single-key, non-nullable column" ideally an integer. What is essentially a foreign key does not figure and there is no easy ...


3

What could explain why this gets created? You created the index. Or the client software you are working with did it for you. Postgres certainly didn't. Do I still need to create another index on my product column to improve performance of queries on it? No. A second index on the same column would be a total waste. It's not even certain the first ...


11

I haven't managed to reproduce this after running your code a few times. I presume that it must happen when a later row gets inserted onto an earlier page in the file though. So the order of operations is (for example) Rows inserted into heap on pages 200, 207, 223 Select statement starts and performs an allocation ordered scan. Finds that the first ...


1

Since you are on 9.3, can you try this rewriting: SELECT participants.contest_number, points.track_id, points.lat, points.lon, points.alt, points.timestamp, points.h_speed, points.v_speed, points.distance FROM ( SELECT track_ptr_id, participant_id FROM tracks_competitiontrack WHERE task_id = 24 ...


0

In order to deploy a shard key in your case (name,age) you need to pre-create an index on (name,age). Shard key can be unique, but I don't see how a unique constraint fits here, for example isn't likely to have two same records? (name:Mary age:26?).


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.


2

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



Top 50 recent answers are included