Defect #1057

Missing indexes in sql server database schema

Added by Van Hoecke Hélène about 4 years ago. Updated about 4 years ago.

Status:NewStart date:07/07/2015
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:CORE
Target version:-
Affected version:4.3.1

Description

There is some missing indexes in the SQL server database schema.

For example, #__sdi_resource and its related tables don't have indexes on foreign keys, which are usefull for the queries executed to build the resources page.

History

#1 Updated by Van Hoecke Hélène about 4 years ago

A solution is to run this query that creates all missing index on foreign keys :

Select
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['
+ tab.[name]
+ ']'') AND name = N''IX_'
+ cols.[name]
+ ''') '
+ 'CREATE NONCLUSTERED INDEX [IX_'
+ cols.[name]
+ '] ON [dbo].['
+ tab.[name]
+ ']( ['
+ cols.[name]
+ '] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
From sys.foreign_keys keys
Inner Join sys.foreign_key_columns keyCols
On keys.object_id = keyCols.constraint_object_id
Inner Join sys.columns cols
On keyCols.parent_object_id = cols.object_id
And keyCols.parent_column_id = cols.column_id
Inner Join sys.tables tab
On keyCols.parent_object_id = tab.object_id
Order by tab.[name], cols.[name]

Also available in: Atom PDF