Tag Info

New answers tagged

-1

Add one composite index with two columns: pid, version: ALTER TABLE auc_daily_histories ADD INDEX pid_version_ix -- pick a name for the index (pid, version) ; And buy new server or move it to AWS RDS.


0

Flushing the query cache would be OK depending on Storage Engine InnoDB spends time micromanaging data changes. This includes crosschecking the query cache. I have a 2-year-old post (Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?) where I recommend running FLUSH QUERY CACHE via the crontab. I also have more recent posts ...


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


0

Setting the two files on two different disks may not help you, because speaking generally, they are only written serially: The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the ...


1

Wouter got, hopefully, useful answers here: https://community.oracle.com/message/12608939 That said, being the Oracle ACE Director for this technology. Oracle XMLDB functionality is nowadays so interwoven with the Oracle database that this is an MANDATORY (install) functionality for Oracle 12c and onwards. It is the basis for much in the database like ACL ...


1

When Orders and Deliveries are both properly indexed, the second query will generally perform better. There are two main considerations here: Generally speaking, and without seeing the details of your tables - what probably happens with the INSERT/UPDATE construct is that it will first allocate a number of pages in the database in the initial INSERT. But ...


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


1

You can specify the name for a clustered primary key if you add it as a separate constraint: CREATE TABLE dbo.YourTable ( idBenchmark int NOT NULL IDENTITY (1,1), .... other columns .... CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED(idBenchmark) ) and then you should have no trouble at all to drop it if needed.


4

Go ahead and drop the clustering key while you're importing data. When you've finished your INSERTs, create the clustering key first, then the PK if it's non-clustered, then any remaining indices. I'm running such scripts at this very moment, and it takes about half as long as inserting into a table which is fully indexed. There's no problem in going ...


2

Your query is using the index to fetch the results but needs to sort them afterwards which is an expensive operation. "scanAndOrder" : true, scanAndOrder is a boolean that is true when the query cannot use the order of documents in the index for returning sorted results: MongoDB must sort the documents after it receives the documents from a cursor. The ...


0

In addition to what Jynus said: be sure your table is physically clustered on Date first. This will make range scans very efficient, so aggregation up to weeks or months will be fast. Even if you choose to instantiate these week- or month-level totals in summary tables, clustering by date will help by making the updates very quick. This kind of situation ...


2

PROBLEM From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column. Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on. SOLUTION What you really need is a single FULLTEXT index with all 3 columns ALTER TABLE ...



Top 50 recent answers are included