Large database tables

Daniel Nilsson

Vote:

We have a customer site in EPi 4.61 were three of the tables in the database seems to be too large considering the amount of content in the site. The site has been active for a few years and has been updated from previous EPi-versions. It has the versioning set to unlimited.

Is the following normal? If not, how can i reduce the size of the tables? Is it perhaps connected directly to the unlimited versioning?

sp_spaceused 'tblIndexDateTime' 511MB
sp_spaceused 'tblIndexInt' 503MB
sp_spaceused 'tblIndexString' 810MB

#25233

Oct 15, 2008 18:16

Vote:

Those tables are used to index certain fields stored in the objectstore (tblitem) and are not used by pageversioning.

The following select will tell you what schemas are most frequently using the tblIndexString, and that would probably give some hints on what areas that are of interest to take a closer look at:

select tblSchema.SchemaId, count(*) as "Count" from tblSchema inner join tblSchemaItem on tblSchema.pkId=tblSchemaItem.fkSchemaId inner join tblIndexString on tblIndexString.fkSchemaItemId=tblSchemaItem.pkId group by tblSchema.SchemaId order by count(*) desc

Replace tblIndexString with tblIndexInt and/or tblIndexDateTime for the other tables.

/johan

#25236

Oct 15, 2008 20:47