New answers tagged

0

I will try to : Create empty table with indexes DISABLE KEYS Load Data myisampack table myisamchk -rq Hopefully myisamchk will build the indexes without ENABLE KEYS


3

If you create an index on (row_time DESC, row_id DESC), then the second case will will act just as the first one. LIMIT will always operate after sorting is taken care of, so when sorting without an index is necessary, it will sort the entire recordset prior to processing it through LIMIT. I wonder what is it you want to achieve? Applying LIMIT over a ...


18

Index A is better for this query. When all the conditions in the WHERE are equality checks except one that is using a range condition or IN operator on a column, then that last column should be last in the index, after all the columns that have an equality check. This allows the optimizer to use an index seek to the first row that matches the conditions and ...


2

There are a number of ways the design of your model can change to speed up this kind of query. a) Materialized path You basically store a path for each node in the tree. Retrieving the sub tree is something like: select ... from category where path like :current_path || '%'; When adding a category somewhere in the tree, the new path becomes the path of ...


2

Try this index for some noticeable improvement: CREATE NONCLUSTERED INDEX [CatIndex1] ON [dbo].[Category] ( [ParentID] ASC ) INCLUDE ( [CategoryID], [CategoryName], [DisplayORder]) I am interested in the time reduction. Please let me know your findings. p.s. consider sorting only once with the ORDER BY if you're running it multiple times. ...


-2

Yes fragmentation will negatively impact performance. Anything over 30% is bad but on a small table still would not have a major impact. But on a small table defrag is fast so might as well do it. That report you posted is 20 or so rows and starts on 96. You could also have > 30% on some big indexes. In rows 1-95 I can pretty much guarantee you have ...


7

I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them. This is a knee-jerk response to a performance problem. Your page counts are less than 10 ! Rebuilding the indexes will be a least help in your case. I would start by reading and understanding How to analyse SQL Server performance ...


1

You can change the tablespace of the index explicitly with alter index command. CREATE TABLESPACE superfastssdtablespace LOCATION '/path/to/super/fast/ssds'; ALTER INDEX name_of_the_index SET TABLESPACE superfastssdtablespace; Before you do that you may (also) want to look at partitioning your table or using BRIN indexes depending on the use case.


1

Usage statistics come from sys.dm_db_index_usage_stats, which tracks the number of execution plans that include an operator touching that index. It's reset on SQL Server service restart, or when the index is modified. Operational statistics come from sys.dm_db_index_operational_stats, which track the number of times the index has actually been touched. It's ...



Top 50 recent answers are included