[VStudio][mySQL] Speed improved A LOT! Reading for Constraints

Speed of openingĀ  a mySQL database that has ForeignKeys improved a lot!

Few people have report that Valentina Studio 5.3 for them is very very slow on open of a mySQL database. After some investigation we have found that problem comes from simple, legal official commands to mySQL server, which return list of constraints. We need this to build the list of FK of a Table or even of a Database. mySQL is incredibly slow on this operation.

We have take a look on other tools and with wonder we have found that they all use next trick: They ask via “SHOW CREATE TABLE T” the SQL string of “CREATE TABLE” command for a Table T, then they parse it to extract info about constraints.

We even have start implement this way also, but few days later was found that mySQL loose huge time collecting statistic. We have disable flag ‘innodb_stats_on_metadata’ and bingo! Speed becomes as fast as it should be.

So we have add into Valentina Studio ability to disable this flag. Added checkbox into “Add Bookmark” and into “Connect To” dialogs. On default it is ON.

Do not worry, this flag and this statistic have no many sense. We do not understand, why it is enabled on default in mySQL.

You can right now download and test beta build with this improvement: http://www.valentina-db.com/download/beta/5.4b5 or newer.

Published by

Ruslan Zasukhin

VP Engineering and New Technology Paradigma Software, Inc

Leave a Reply