Download the PHP package simbiat/optimize-tables without Composer
On this page you can find all versions of the php package simbiat/optimize-tables. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
FAQ
Example:
In general, it is recommended to use always a project to download your libraries. In an application normally there is more than one library needed.
- Some hosting areas are not accessible by a terminal or SSH. Then it is not possible to use Composer.
- To use Composer is sometimes complicated. Especially for beginners.
- Composer needs much resources. Sometimes they are not available on a simple webspace.
- If you are using private repositories you don't need to share your credentials. You can set up everything on our site and then you provide a simple download link to your team member.
- Simplify your Composer build process. Use our own command line tool to download the vendor folder as binary. This makes your build process faster and you don't need to expose your credentials for private repositories.
Informations about the package optimize-tables
[!WARNING]
This library has been replaced by DB Maintainer and is no longer maintained, but latest release should still work.
Database Tables Optimizer
This library is developed to allow bulk optimization of tables in MySQL (MariaDB and, potentially, other forks). While MS SQL has its Maintenance Plan Wizard, there is nothing like that for MySQL.
Benefits
One may think that simply getting a list of tables and running OPTIMIZE against them is enough, but in reality, it's not that simple:
- Not all tables support OPTIMIZE.
- There are some special parameters that may improve OPTIMIZE results in some cases.
- Some tables may benefit from CHECK and REPAIR commands as well. In fact, it's useful to periodically run CHECK to avoid potential corruption of a table.
- ANALYZE is quite a useful command as well, allowing to update MySQL statistics that may improve some of SELECT.
- MariaDB 10.4+ and MySQL 8.0+ also support histograms that may improve SELECT in cases when a column does not have indexes for some reason.
- No matter how useful these commands are, there are cases when you do not need to run them, especially on large tables, since they may take quite some time to complete. The simplest case: there have been no or very few changes since the last time the OPTIMIZE was run.
This library aims to cover all these points in as smart a manner as was possible at the moment of writing. For details refer to the Usage section of this readme or comments in the code.
Requirements
- SimbiatDB
- MySQL or MariaDB
Usage
To use the library, you need to establish connection with your database through DB Pool library or pass a PDO object to constructor and then call this:
The output will look like this:
schema means the name of the database/schema you want to analyze. This command will show you the list (as an array) of tables with some statistics and a list of optimization commands they can/should be run for each table.
After this you can run them manually if you like some control. Alternatively, if you want to use the library in a cron or other scheduler you can run this:
This will analyze the tables and then run the suggested commands. The result of the function (by default) will be an array of log entries, where array keys are UNIX micro timestamps:
This will also create a tables.json file with some more statistics, that will look like this:
There is also the possibility to get statistics from the last run by:
This will show all tables that were either compressed or had changes in their size statistics.
In case you want these statistics to be returned instead of logs, you run this:
If you want to work with regular booleans, you can send one extra true:
If you just want to get the suggested commands, run this to get an array of arrays:
This is it — easy to use. There are also some settings, that will allow you more control on what is done by optimize().
Settings
All settings can be chained together. To check the current setting — access the respective variable.
| Setter | Variable name | Description |
|---|---|---|
setThreshold(float $threshold) |
$threshold |
Set a threshold for fragmentation of table data. If the current value equals or is greater — table will be suggested for OPTIMIZE. |
setSuggest(string $action, bool $flag) |
$suggest |
Flag allowing to suggest a command if flag is set to `true`. `$action` stands for appropriate command: `ANALYZE`, `CHECK`, `COMPRESS`, `OPTIMIZE`, `REPAIR` and `HISTOGRAM` (special case of `ANALYZE`). |
setJsonPath(string $jsonpath) |
$jsonpath |
Path to save statistics, which are necessary for all consecutive runs. By default, the file `tables.json` will be written to the system's temporary folder. |
setDefragParam(string $param, float $value) |
$defragParams |
MariaDB 10.1.1 implemented the `innodb_defragment` flag with a set of settings for it. All of them can be set through this function if non-default is required. Their names can be sent without the `innodb_defragment` prefix. |
setMaintenance(string $table, string $setting_column, string $setting_name, string $value_column) |
$maintenanceFlag |
Library can take quite some time to run, and some commands may lock tables (fully or not), so it's advisable to prevent applications from communicating with them. Usually this is handled by having some kind of system parameter identifying an ongoing maintenance. `$table` stands for table name, `$setting_column` — the name of the column where to search for `$setting_name`, that is the name of the maintenance flag. `$value_column` is the name of the column, which we will be updating. |
setDays(string $action, int $days) |
$days |
Set the number of days to wait since the previous run of an `$action` (same as in `setSuggest`). Unless the designated amount of time has passed, the action will not be suggested for the table. |
setExclusions(string $action, string $table, $column = NULL) |
$exclude |
Exclude table(s) from processing for a particular `$action`. In the case of `histogram` you can also send a list of columns to exclude from preparing histograms if we are on MySQL 8.0+. To avoid providing excessive columns to histograms' exclusion list, adding only one table at a time is allowed. |
