Tag Info

New answers tagged

1

The best query very much depends on data distribution. You have many rows per date, that's been established. Since your case burns down to only 26 values in the result, all of these solutions will be blazingly fast as soon as the index is used. The partial index below will be a bit faster if you have many NULL values. For more distinct values it would get ...


0

Try running this first, and selecting it into a temp table: INSERT INTO #TempProbChild Select * FROM ProblemChild WITH (INDEX(IDX_geog)) WHERE Geog.Filter(@geog) = 1) Then on that subset, run your .STIntersects. Select count(*) FROM #TempProbChild WHERE Geog.STIntersects(@geog) = 1) I've found that on very large datasets (hundreds of millions of rows ...


3

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error. http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2095331 If you specify schema.index, then Oracle attempts to enforce the constraint ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


0

I've done a lot of experimenting and here are my findings. GIN and sorting GIN index currently (as of version 9.4) can not assist ordering. Of the index types currently supported by PostgreSQL, only B-tree can produce sorted output — the other index types return matching rows in an unspecified, implementation-dependent order. work_mem Thanks Chris ...


0

From the postgresql documentation: CLUSTER can re-sort the table using either an index scan on the specified index, or (if the index is a b-tree) a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information. Your index on labelDate is a btree.. ...


12

The performance will be the same. The optimizer will recognize this and create the same plan. On the other hand I wouldn't say they are equal. The first form is far more readable and generally expected. For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query. You should be able to ...


9

They are semantically identical and the optimiser should have no trouble recognising this fact and generating identical plans. I tend to put conditions referencing both tables in the ON and conditions referencing just one table in the WHERE. For OUTER JOINS moving the conditions can affect the semantics however.


2

Here are a few quick tips which can help improve your performance. I'll start with the easiest tip, which is almost effortless on your part, and move on to the more difficult tip after the first. 1. work_mem So, I see right off-hand that a sort reported in your explain plan Sort Method: external merge Disk: 5696kB is consuming less that 6 MB, but is ...


0

For long (mediumtext) you have to specify how much of the string you want to index. If you want the first 100 bytes in the column to be indexed: CREATE INDEX ix_logs_create_date ON logs (create_date(100)) USING BTREE; May I ask why you named a column of type long create_date?


1

Index usage with text_pattern_ops (as well as with the default operator class when using the C locale) depends on the binary representation of character data. citext stores original values with the case preserved, so there must be a problem with that ... Either way, citext or text, you can make it work with an expression index: CREATE INDEX people_name_idx ...


1

Index is case insensitive in your case, but optimizer is clever enough to use it efectively anyway. In first explain, mysql uses ref access method - that means that it is able to directly fetch rows with given value. In second explain, it says "using where" and uses range access - it takes the string, fetches all values from index searching for that string ...


1

Note the link you gave on the BINARY operator where is it says the following Posted by Yann Neuhaus on July 20 2005 12:34pm Per default the search operation in not case sensitive, example, looking for 'HYPE BEAU' returns 'Hype Beau' in the table product : mysql> select prod_name, prod_id from products where prod_name = 'HYPE BEAU'; ...


2

Indexing Expressions I believe that the solution you are looking for pertains to building an index on an expression, rather than on the original data itself. For outside references, you can consult Wikipedia or the Oracle documentation, where the link here contains more info and subsequent links in the 'Function-Based Indexes' section. There are associated ...


4

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows. In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse SELECT * FROM MYTABLE WHERE my_column > to_timestamp( ...


0

I believe this will do the job but the databases I have access to at the moment do not have text indexes. I will confirm later today. SELECT * FROM ALL_INDEXES WHERE index_type = 'CONTEXT'


0

There are 4 types of Text indexes in Oracle, owned by the CTXSYS user (installed optionally), so you can simply query [DBA|ALL|USER]_INDEXES and filter rows by the ITYP_OWNER and ITYP_NAME columns: select * from dba_indexes where ityp_owner = 'CTXSYS' and ityp_name in ('CONTEXT', 'CTXCAT', 'CTXRULE', 'CTXXPATH') ; Index types can be found in the ...


3

The old index stays in place, is maintained and being used by queries. Only at the end of the index build does SQL Server alter the metadata in such a way that the old index disappears and the new one is set in. There is not a single millisecond without an index. (But there is a short-lived Sch-M lock twice during the online build.)


4

why is the query optimizer not able to use the index for deleting when the subquery version, while it is while using the join version? Because the optimizer is/was a bit dumb in that regard. Not only for DELETE and UPDATE but for SELECT statements as well, anything like WHERE column IN (SELECT ...) was not fully optimized. The execution plan usually ...


2

here is the answers for two of your questions Optimizer is not able to use index because the where clause changes for every row. The delete statement will look something like this after it pass the optimizer delete from VARIABLE_SUBSTITUTION where EXISTS ( select BUILDRESULTSUMMARY_ID from BUILDRESULTSUMMARY where BUILDRESULTSUMMARY.BUILD_KEY = ...


4

Try to use this. ;-) use DB; EXEC sp_MSforeachtable @command1=N'print ''?'' DBCC DBREINDEX (''?'', '' '', 80) '; Double quotation marks will escape the following quotation mark. Here an example of sqlcmd: sqlcmd -S YOURSERVER -Q "use net_temp_test; EXEC sp_MSforeachtable @command1=N'print ''?''';"


0

I just found another solution to this problem based on this forum post -- EXTRACT(timestamp with time zone) isn't immutable, but EXTRACT(timestamp) is. So it is possible to create a table with CREATE INDEX ON table (EXTRACT(MONTH FROM timestamp AT TIME ZONE 'UTC')).


0

No: local instance can't know when remote tables are updated. Also, I suggest you use SPIDER instead of CONNECT.


1

The optimizer seems to think that your index is not selective enough. I think that you can do one or more of the following things: ANALYZE TABLE bets; ALTER TABLE bets ADD INDEX bets_time_amount (time, amount); /* this is a covering index */ add a FORCE INDEX to your SELECT


0

MyISAM? or InnoDB? 4 FULLTEXT indexes will work in either engine. You could find out by creating an empty table and running that SELECT against it. It should give you an error if the set of indexes you tried is wrong. The ALTER to add/drop indexes.


0

Consider using ENUM for status, event, type. ENUM is 1 byte, not many. The optimizer is using only company_id from the index. Consider adding: INDEX(company_id, start_time) ICP helps a little, but having fields in the index fields that can be directly used helps a lot more.


2

processing_date is not in the index, so it cannot use an index only scan. Create the index on (presentation_code, pct_id, processing_date, actual_cost) so that it can use an index only scan, and in that order so that it can efficiently use the sort order of the index to do the grouping.


1

You can use SolrMongoImporter Download solr-mongo-importer jar file Download mongo-java-driver jar file Place both jar's files in your Solr libraries folder Include the libraries in your solrconfig.xml Example of usage: <lib path="../../dist/solr-mongo-importer.jar" /> <lib path="../../dist/mongo.jar" /> <?xml version="1.0" ...


3

You forgot to mention that you installed the additional module pg_trgm, which provides the similarity() function. Similarity operator % First of all, whatever else you do, use the similarity operator % instead of the expression (similarity(job_title, 'sales executive') > 0.6). Much cheaper. And index support is bound to operators in Postgres, not to ...


0

Even if the data is not stored on the leaf level, the index will provide your database engine with a very exact location in the data pages where to look for the results. I.e. instead of browsing all of your table's data pages for relevant data, your index seek first browses fewer and smaller pages, ideally with a hierarchy (so that it browses even fewer). ...


0

allcaller_allcaller_logs starts with start_time but in your query start_time is checked by BETWEEN - thats range scan, not direct ref, so the query cannot simply use remaining columns (I suppose you use MySQL 5.5, in 5.6 there is Index Condition Pushdown Optimization which would be able to utilize that index better). Your index seems to "cover" your query on ...


7

You are correct in your description but the conclusion is not correct. It may still be faster to use the index anyway even if it has to go off to the table for the rest of it. The data pages contain many fewer rows than the index so there's lots of extra I/O when scanning. Generally the index will tend to give you benefits if it selects few rows even if ...


0

I had forgotten about this question, sorry. But we resolved the issue. Using a MERGE statement reduced the query time to under an hour. Disabling indexes had no effect.


3

Determine the largest current ID value: select max(TIMESHEET_ID) from PROJECTMGMT.TIMESHEET then reset the identity value: alter table PROJECTMGMT.TIMESHEET alter column TIMESHEET_ID restart with <whatever the max value is + 1>


0

GENERATE BY DEFAULT means that you can provide a value or you can allow DB2 to provide a value. If you use GENERATE BY DEFAULT and you provide a value, you may need to ALTER TABLE to reset the identity as DB2 will retain the last value it was at (rather than what you inserted into the table). GENERATE ALWAYS means just that. Only DB2 can create values for ...


0

The general index format used by MongoDB's included storage engines as at 3.0.x (MMAPv1 and WiredTiger) is B-tree, however there are more nuances in the technical implementation. MongoDB 3.0 introduced a storage engine API which separates the concerns of storage formats (i.e. data & index representations on disk and in memory) from the core server ...


0

InnoDB has no tool to do anything like what you describe. InnoDB has a limit of 767 bytes per column in an index. That has room for a VARCHAR(255) utf8 or a VARCHAR(191) utf8mb4. Also, InnoDB wants to put long VARCHARs in a different block if the whole record is bigger than about 8KB. Will this be common? (Don't blindly use VARCHAR(500) when you can ...


0

You could also create the indexes on other tablespaces than the default. These tablespaces could point to disks that are not redundant (just recreate the indexes if they fail), or are on faster arrays. You might also consider partitioning the table using the same criteria as your partial indexes. This would allow for the same speed as the index when ...


3

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work: A snapshot of the table is defined. ...


1

My understanding is the snapshot should reside on the index file drive. Is this correct? It resides on same file where Index file resides unless you use Sort_in_tempdb option during rebuild. From BOL When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. If the SORT_IN_TEMPDB option is set to ...


0

"Near" is not relevant. This is because of the caching of both data blocks and index blocks in the buffer_pool. The caching leads to writing/reading in orders that your logic does not account for. Do not use the internal auto-generated PRIMARY KEY; always explicitly specify a PRIMARY KEY, even if you add the secondary keys later. If your PRIMARY KEY is ...


3

Constraints and Indexes serve two different purposes in the database. Constraints prevent data that violates the constraints from being stored in the database. This protects future users of the data, but it limits flexibility and adds overhead. For foreign key constraints such as the ones you have presented, the overhead is usually small, compared with ...


4

Use KEEP INDEX. drop table demo purge; create table demo (id number, constraint demo_pk primary key (id)); alter table demo drop constraint demo_pk keep index; select index_name from user_indexes where table_name = 'DEMO'; INDEX_NAME ------------------------------ DEMO_PK


0

I was able to make this work after altering your model. Hopefully this still fulfills the use case you are targeting. To bypass the dynamic nature of your fields, I created a field named "field" and a field named "value". This allows me to create a multikey index on known fields. db.myColl.insert( { "user_id" : NumberLong(24), "request_datetime" : ...


3

I don't think there is a supported way to do this. Consider the comments so far. If you are concerned about effects on concurrent transactions, there is a code example in the manual: To recreate a primary key constraint, without blocking updates while the index is rebuilt: CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); ...



Top 50 recent answers are included