David Smithstein

<div>
Thanks David, &nbsp;that gives me a good direction to start heading in.
</div>


<div>
You're welcome!<br />
<br />
Once your make some progress on your design, another trick you can use is to place EXPLAIN in front of any SQL statement for granular debug info about how fast the SQL statement will work against your real data.<br />
<br />
Another trick is to use MariaDB, rather than MySQL. This may already be occurring, as many Distros are dropping installation of MySQL, so MariaDB packages are installed when mysql-* packages are installed.<br />
<br />
Once you have MariaDB installed, setup page compression using LZ4 (or if you're highly tech savvy, build ZSTD compression).<br />
<br />
Page compression produces massive performances uplift.<br />
<br />
Another trick, use NVMe drives as they're far faster than mechanical drives. They're also more expensive, so better to design your database tables + SQL statements to work well with mechanical drives from the start.<br />
<br />
Note: Open additional questions as you get to a point where you'd like to tap into other people for their design expertise.<br />
<br />
EE has some very smart SQL EE'ers.
</div>


<div>
<div class="content wysiwyg-content">
Does anyone understand MySQL row formatting enough to makes some recommendations for best performance? &nbsp;<br />
<br />
There are about 401 tables, and the vast majority mostly contain 10-40 fields comprising of varchar, longtext, datetime, timestamp, int, and tinyint fields. &nbsp;The exception is about 3 or 4 tables that also contain BLOB data fields holding document file types. .doc, docx, .pdf. .xlsx, etc. &nbsp; The data being entered is consistent with what would be otherwise entered on a business form for a quality management system, or in a material management database. &nbsp;Most of the databases are version 5.7, although we are in the process of upgrading everyone to MySQL 8.0 <br />
<br />
Is there a default row format I should be using that is likely to give me the best performance? &nbsp;And what circumstances might make an exception to that default setting?
</div>
</div>


Does anyone understand MySQL row formatting enough to makes some recommendations for best performance?  

There are about 401 tables, and the vast majority mostly contain 10-40 fields comprising of varchar, longtext, datetime, timestamp, int, and tinyint fields.  The exception is about 3 or 4 tables that also contain BLOB data fields holding document file types. .doc, docx, .pdf. .xlsx, etc.   The data being entered is consistent with what would be otherwise entered on a business form for a quality management system, or in a material management database.  Most of the databases are version 5.7, although we are in the process of upgrading everyone to MySQL 8.0 

Is there a default row format I should be using that is likely to give me the best performance?  And what circumstances might make an exception to that default setting?

MySQL Row Formatting Recommendations

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.