Tag Info

New answers tagged

2

The spatial index, as with other objects, have their own script <object> as menus and need to be done separately. This can be done to the clipboard and pasted to the query window generated by the script table as command. I can't confirm at the moment, but I suspect that the primary key and other constraints come out in the script table as options ...


4

Through version 9.3, the indirection array used for sorting had to fit in a single 1GB memory allocation. This created an artificial limit on the number of tuples which could be sorted in memory. Once that limit was reached, it had to switch to a disk sort, even if there was memory left over. This restriction was removed in version 9.4.


0

Index usage depends on the structure of you data and the selectivity of you data. If you make queries that you don't use them they are expensive. If you use them effectively they are great tool. There is a query planner and query planner analytics(EXPLAIN) of just planning or planning and executuion of commands (EXPLAIN ANALYZE). And there is a great ...


1

I wrote some posts about RAM disk and temp tables Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk? Jan 17, 2012 : How can I optimize my mysql setup to create my index faster? Sep 23, 2011 : skip copying to tmp table on disk mysql The basic idea would be to set set tmp_table_size and max_heap_table_size to the ...


1

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko) When you update an indexed column, changes must migrate through this architecture as follows: From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1 From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file) If you are ...


1

No. How would the query processor recognize that it should use the materialized view. The materialized view is another object. See http://www.postgresql.org/docs/9.4/static/rules-materializedviews.html In part that explains: "When a materialized view is referenced in a query, the data is returned directly from the materialized view." So materialized ...


0

You cannot move just indexes to another location. You have to move both data and indexes. When you use innodb_file_per_table, this will create a file with the extension .ibd. For example, if you have an InnoDB table as follows datadir /var/lib/mysql database mydb table mytable The physical files are the following /var/lib/mysql/mydb/mytables.frm ...


5

I think the script in question is sp_BlitzIndex. The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question: Is it valid for those columns to all really contain nulls? When tables are ...


1

I'm not sure what specific problem the script is trying to point out, but no, it is not best practice to unilaterally prevent all columns from allowing nulls. Allow NULLs when you need them, and don't allow them when you don't. There is no right answer to this; it all depends on how you are using the data and how likely it is that you'll have all of the ...


0

U should try to partition your table, try this : http://dev.mysql.com/doc/refman/5.1/en/partitioning-management.html I really suggest you to use MyISAM instead. Such a scenario i had in my workplace for some big tables that we use to store data. I have done a script that inserts every night records from the main_table to an main_table_bak a backup of the ...


0

no, pgstrom cannot do that. it only able to scan relations - it is not able to join. what you an do is pack an operator into a GPU routine but = is not a good candidate for that.


0

Delete just put a flag on the document that is deleted. I am not sure but i think it doesn't reorganize the indexes at the same time, if that is your question, because reorganize the b-tree is an expensive operation.


-2

One clustered index is not enough, because it will do ClusteredIndexScan(logical operator in Exeution Plan) So, if your version of SQL Server is above 2008 then this may help: CREATE INDEX Idx_Name ON myTbl(UserName, StartDate, EndDate) INCLUDING(Value1, Value2, Value3, Value4, Value5, Value6) this will allow Query Optimizer to use IndexSeek If your ...


6

From the limited information given, a suitable non-unique clustered index is: CREATE CLUSTERED INDEX CX_dbo_myTbl_StartDate_EndDate_Username ON dbo.myTbl (StartDate, EndDate, UserName); That said, there are all sorts of other considerations that might mean a different choice would be optimal for the workload as a whole. If you improve your question with ...


0

do you have a filter on the form maybe (look in the edit mode of the form)? Is it a subform which is filtered by the main? Has the change of the number been distributed to ALL tables concerned? Maybe try to create a quick-and-dirty new form to see if the record can be seen there. This might rule out some problem sources. I think an image might help ...


3

in your case it's a choice between bad and worse. gender is low selective (read MySQL has to read at least a half of index), if age comes first, gender part won't be used at all (because of < ). (gender, age) seems to be a bit better anyway. Check with SHOW STATUS LIKE 'Handler%' which index makes MySQL to read less rows mysql> FLUSH STATUS; ...


4

A view is just a "saved query". The indexes on the base table are still used whenever you access the view. You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to ...


1

The selectivity has changed with the added selections.So now the optimizer will think that an intersection of indexes is more efficient than just one index.Compound indexes have a cardinality that matches all their columns,it`s not separate for column so MySQL will try to reduce this selection with an index intersection.Find out which column is the most ...


1

An alternative to Roger's answer, would be to LEFT JOIN to TblZ. Personally I think for the example you've posted, an APPLY is probably not the most appropriate method of data retrieval, however it would be worth testing performance against the APPLY to see which is quicker. I'd also recommend not selecting *, simply because you could be pulling columns ...


2

For starters, you can remove multiple references of the same table: Select A.w, oa.f as valueZA, oa.k as valueZB, From DatabaseA.TblA A LEFT JOIN DatabaseA.TblB B A.x = B.x outer apply ( Select Z.f, Z.k From DatabaseZ.TblZ Z where Z.w = A.w ) oa where A.isActiveBit = '1'; You can replace outer with cross, depending on the ...


0

I'm trying hard to understand how indexes work. A very good introduction to how database indices are used is the SQLite query planner page: https://www.sqlite.org/queryplanner.html (while written for SQLite, database indices in most other database management systems work in the same way).


7

none of the columns are mentioned Yes they are, right here: where pd.active and pd.fk_status = 1 that matches the condition on the index and thus the index can be used to support the counting of the rows. Reading through all the rows in the index should be faster than doing a seq scan on the process_data table. Why it's not using the other index I ...


2

Since you stopped the index creation, the index was not created. However, the space it had reserved already (10GB) was still added to the database size. SQL Server will not automatically shrink the database file size back down unless you explicitly instruct it to (which is not recommended unless you are about to run out of space). You can add up to 10GB of ...


1

In addition to aasims' answer, the only major difference here being a Unique Index is not a database object. Whereas a unique Constraint is an object. However other than that there are no other major differences: They both achieve the same performance They both achieve the same end goal. EDIT: Why would you use Unique Index vs Constraint, what's the ...


2

Fact is that there is no practical difference between a unique constraint and a unique index. Querying sys.objects, you will find unique constraint is listed as a constraint object and its related index can be found while querying sys.indexes, where it is marked is_unique = 1 as well as is_unique_constraint = 1. On other hand, for a unique indexes ...


2

Variable length character types only store actual content put into it and odn't keep the extra space allocated (thus the name "variable length"). As a result, when you INCREASE the size of a variable length column, the data storage doesn't actually change (in the table or the index). The SQL engine is smart enough to know that and doesn't do anything to ...


0

You can turn off the system stoplist and create your own, eg ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = OFF CREATE FULLTEXT STOPLIST myCustomStoplist; ALTER FULLTEXT STOPLIST myCustomStoplist ADD 'someWord' LANGUAGE 'English'; ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = myCustomStoplist If you create an empty stoplist, it means all ...


2

When you script the index you need to make sure the scripting option is not set to SQL Server 2005. Tools / Options / SQL Server Object Explorer / Scripting / Script for server version I suspect this is the problem (and it happened to me, too).


2

Yes, you can stop a reorganize and it won't cause a big rollback like you are talking about. You will be left with where the operation left off (that's a good thing). It's a rebuild that would have the rollback behavior.


3

In this special case the column actually indexed is irrelevant for the query at hand. You can pick any column. I would pick something else than uploaded_at, which is useless. Some column that may be useful for other queries and is not bigger than 8 bytes ideally. CREATE INDEX foo ON table bar (some_col) WHERE uploaded_at IS NULL; If you have no use case ...


4

Yes, as ypercube already said. The only thing to look out is a wild discrepancy in the column actual data size. For instance if NC_Index1 is some 1Mb, and NC_Index2 is 200GB then you can introduce some potential scan perf problems. But, frankly, the chances of this being the case are, basically, 0. Or NULL, depending on your prefs.


13

Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion: WHERE [serialNumber] = 137802 According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of ...


5

You need to baseline a whole workload and measure before/after. With just the numbers above I have no idea what they mean. The questions I would have include: There were 124,000 updates but did they take 10 times longer than they used to? Did they block users? Is there more activity because people can do more because it's faster, or just a natural ...


1

consider using the citext data type available in contrib. you don't have to do this upper / lower nightmare anymore. for the regular expression type you can use gist along with gist_trgm_ops. it will boost regular expressions nicely.


1

INHERITS itself will never inherit indexes. it will inherit columns only.


2

i have used PostGIS for over a decade now and I can tell you for sure that there is no match for it in the NoSQL world. how many rows do you have? how large is the thing? Mongo is definitely not going to make you happy. I am pretty sure you did something fishy on the PostgreSQL side to even consider using Mongo. Let us fix it ...


1

it is important to know, which queries you are running. otherwise it is close to impossible to give you any real advise. however, there are two things you should know: UPDATE benefits nicely from a thing called FILLFACTOR. it can speed up things a lot. the order of columns does have an impact on the size of the table ...



Top 50 recent answers are included