Tag Info

New answers tagged

0

As the results of the query will be the same for existing data (unless you change the rules), have you thought of storing the results of your query in another table along with the max log_id value from the source? That way, when analysing the next chunk of data, you can add a filter to the query to select only data with log_id values higher than those ...


1

As it states on the ALTER INDEX documentation, using ALTER INDEX ...REBUILD is equivalent to DBCC DBREINDEX. ALTER INDEX has far more functionality in terms of other arguments and DBCC DBREINDEX is deprecated, however the performance issues you face should not be related to using the old method of re-indexing.


1

That is because SQL Server reads entire pages instead of records and when a index is used SQL Server might need to follow the bookmark to fetch the fields that are not included in the index. So if your query isn't selective enough, it might end up reading all the pages of the index, and following the bookmarks to read all the pages of the actual table too. ...


2

version <= 11.2? No.* But in 12c, there is a new feature called Asynchronous Global Index Maintenance. The DROP PARTITION operation completes immediately, leaving the global index in usable state. The trick is, that index entries are not maintained, they are orphaned, and these orphaned entries will be cleaned up later. The cleanup process happens ...


0

I think you might still be able to use the (employee_id, department_id) index, but you'd have to include a 'dummy' line in the where phrase, like: "employee_id = employee_id) having an index on (employee_id, departemnent_id), having to search / restrict only on a department_id knowing it won't use the index since wrong order (or things have changed by ...


3

This will give you the estimated time remaining select session_id ,command ,wait_time ,CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar (10)) + ' sec' as running_time ,percent_complete ,CAST((estimated_completion_time %60000)/1000 as varchar (10)) + ' sec' as est_time_to_go ...


1

I missed that you are already adjusting for maintenance_work_mem in my first read. I'll leave quote and advice in my answer for reference. Per documentation: maintenance_work_mem (integer) Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 ...


4

That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most. But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge ...


0

In the first way, you are sort-of correct. Here is why I say soft-of. InnoDB has a Clustered Index, known internally as gen_clust_index.Basically, it is a rowid index that is created for the InnoDB table under-the-hood (or if you are from Jersey City, NJ like me, created in-the-hood). These rowids are attached to all secondary indexes. Creating a PRIMARY ...


0

Well, with these Indexes, there shouldn't be any issue. How many rows does your table have ? how many joins you use in your queries, If you're using joins, do those tables have appropriate indexes ? The above mention queries should almost not have any issues. Post the actual queries you're running which are slow. Although, If you still want to go ...


2

First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access. The commenters ...


2

You need to analyze the wait chain. What is likely happening is that the OIB has completed the first two phases (perhaps long ago), and is now waiting for user queries to drain in order to do the final phase. One or more user queries is blocking OIB final phase, and as OIB has requested an SCH_M lock it places every other user queries in the wait list. The ...


2

move to SQL Server 2014 that offers updateable clustered columnstore index. if stuck to 2012, use partition switch to update the nonclustered columnstore index.


2

I've just did a test, and apparently it forces the query not to use any index: mysql> explain select email from B2 where length(email)>30; +----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ...


0

Index names need to be unique in the entire database; indexes are not part of table so they need to be uniquely identified. The Interbase 6.0 Language Reference says: CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col …]); ... index Unique name for the index The Firebird Book, 2nd Edition by Helen Borrie says: ...


0

First, sorry if I have a bad English. Even if you dropped the constraints from the table you are trying to delete, the constraints of any children tables will affect the performance of the parent table. This occurs because the database needs to do an select on all the foreing keys pointed to the main table each line of delete to guarantee the integrity of ...


0

(Not an answer, but too clumsy for a comment.) have an index on process_group_id, start_timestamp, end_timestamp. However the query does not appear to use anything but the process_group_id part of the index. It is actually using start_timestamp although it does not say so. What was the key_len? That may give a clue. Also try EXPLAIN FORMAT=JSON ...


6

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter. But if you have a few million records within 200km, then it could be significantly worse. You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the ...


1

SUGGESTION #1 Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings: innodb_buffer_pool_instances=2 # If your Server is DualCore innodb_buffer_pool_instances=4 # If your Server is QuadCore innodb_buffer_pool_instances=6 # If your Server is HexaCore innodb_buffer_pool_size=90G I ...


2

Index Hoarder is telling you that historically this index is not being used. Therefore, the maintenance of the index is a cost in CPU, I/O, and storage, but you are not getting any performance benefit from those indexes. A general policy of considering Foreign Keys for indexes is good general plan, but an index that is not used should most likely be ...


0

There are multiple reasons for a query to run slowly. May be because of your engine that is not fine tuned, but suppose your engine is well tuned. Begin by printing to a file the query plan of your sql query. Copy the query and open DBACCESS. Run "set explain on" and paste your query and execute it in the "Query language " of DBACCESS utility. Check the ...


4

Run ANALYZE after the index has been added. And make sure the column deprovision has statistics. How to verify? Basic statistics in pg_class: SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE oid = 'schema_defs'::regclass; Data histograms per column in pg_stats (pg_statistics): SELECT attname, inherited, n_distinct, ...


0

Percona themselves have written a tool for this very purpose - it's called pt-index-usage and is to be found here. From the DESCRIPTION: This tool connects to a MySQL database server, reads through a query log, and uses EXPLAIN to ask MySQL how it will use each query. When it is finished, it prints out a report on indexes that the queries didn’t ...



Top 50 recent answers are included