New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Database optimization #738

Closed
PaulBender opened this Issue Jun 9, 2014 · 8 comments

Comments

Projects
None yet
4 participants
@PaulBender
Member

PaulBender commented Jun 9, 2014

The database needs to be optimized to save space:

  • Convert int(1) columns (used for yes/no options) to tinyint(1) (smallint on PostgresSQL)
  • Convert bigint(30) columns (used for datelines) to int(10) unsigned
  • Drop useless columns oldgroup (awaitingactivation table) and status (forums table)
  • Add keys/indexes in several places
  • Combine keys/indexes in several places
  • Change numvotes (polls table) to an int
  • Change stylesheet (themestylesheets table) to longtext

Internal thread: http://community.mybb.com/thread-154625.html

@PaulBender PaulBender added this to the 1.8 Beta 2 milestone Jun 9, 2014

@PaulBender PaulBender self-assigned this Jun 9, 2014

@dragonexpert

This comment has been minimized.

Show comment
Hide comment
@dragonexpert

dragonexpert Jun 10, 2014

Contributor

I can work on this over the next couple days.

Contributor

dragonexpert commented Jun 10, 2014

I can work on this over the next couple days.

@JN-Jones

This comment has been minimized.

Show comment
Hide comment
@JN-Jones

JN-Jones Jun 10, 2014

Contributor

As Paul assigned it to himself I think he plans to do it ;)

Contributor

JN-Jones commented Jun 10, 2014

As Paul assigned it to himself I think he plans to do it ;)

PaulBender added a commit that referenced this issue Jun 16, 2014

Working on #738 Database optimization
All int(1) are now tinyint(1) (smallint on PostgresSQL)
@PaulBender

This comment has been minimized.

Show comment
Hide comment
@PaulBender

PaulBender Jun 16, 2014

Member

First part is done, int(1) columns are now tinyint(1) (smallint on PostgresSQL)

Member

PaulBender commented Jun 16, 2014

First part is done, int(1) columns are now tinyint(1) (smallint on PostgresSQL)

PaulBender added a commit that referenced this issue Jun 17, 2014

Working on #738 Database optimization
Removing old columns
@PaulBender

This comment has been minimized.

Show comment
Hide comment
@PaulBender

PaulBender Jun 17, 2014

Member

Second part is done, the columns oldgroup (awaitingactivation table) and status (forums table) have been removed.

Member

PaulBender commented Jun 17, 2014

Second part is done, the columns oldgroup (awaitingactivation table) and status (forums table) have been removed.

@JN-Jones

This comment has been minimized.

Show comment
Hide comment
@JN-Jones

JN-Jones Jun 17, 2014

Contributor

You should also remove "status" from the "_db_inserts.php" files as they still try to insert a value for the removed columns

Contributor

JN-Jones commented Jun 17, 2014

You should also remove "status" from the "_db_inserts.php" files as they still try to insert a value for the removed columns

PaulBender added a commit that referenced this issue Jun 17, 2014

Working on #738 Database optimization
Converted numpolls to int, removed status from inserts file
@PaulBender

This comment has been minimized.

Show comment
Hide comment
@PaulBender

PaulBender Jun 17, 2014

Member

I've changed numvotes (polls table) to an int and removed status from the inserts file.

Member

PaulBender commented Jun 17, 2014

I've changed numvotes (polls table) to an int and removed status from the inserts file.

PaulBender added a commit that referenced this issue Jun 17, 2014

Working on #738 Database optimization
Changed some columns to smallints

PaulBender added a commit that referenced this issue Jun 18, 2014

PaulBender added a commit that referenced this issue Jun 19, 2014

Working on #738 Database optimization
sid in templates must be signed, not unsigned

PaulBender added a commit that referenced this issue Jun 19, 2014

Working on #738 Database optimization
Add indexes in several places and combined indexes in other places

PaulBender added a commit that referenced this issue Jun 19, 2014

Working on #738 Database optimization
Changed spider columns to smallints and changed stylesheet to longtext

PaulBender added a commit that referenced this issue Jun 20, 2014

Working on #738 Database optimization
Changed last few columns to smallints

PaulBender added a commit that referenced this issue Jun 20, 2014

Working on #738 Database optimization
All bigint(30) are now int unsigned
@PaulBender

This comment has been minimized.

Show comment
Hide comment
@PaulBender

PaulBender Jun 20, 2014

Member

Last part is done, bigint(30) columns are now int unsigned

This is now finished.

Member

PaulBender commented Jun 20, 2014

Last part is done, bigint(30) columns are now int unsigned

This is now finished.

@PaulBender PaulBender added the fixed label Jun 23, 2014

@PirataNervo

This comment has been minimized.

Show comment
Hide comment
@PirataNervo

PirataNervo Jun 23, 2014

Contributor

I've been testing my forums for some time today and didn't find any issues (any issues I can think of are due to the max limits of the integers but I don't think you changed any where the maximum could be reached in normal circumstances).

Contributor

PirataNervo commented Jun 23, 2014

I've been testing my forums for some time today and didn't find any issues (any issues I can think of are due to the max limits of the integers but I don't think you changed any where the maximum could be reached in normal circumstances).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment