CODEWITHSUNDEEP
mysqldata-migration
claywhipkey
claywhipkey

Reputation: 907

Faster method for migrating AND processing data between MySQL databases

I am trying to normalize a schema. I have one MySQL database used in a CMS and the data schema is very problematic. For example, in order to get all properties and sub-properties of an entity, I have to query several tables and also do some post-DB processing. There are a couple sub-properties which are stored in a JSON string in one field, and when you parse that JSON it gives you foreign key IDs to another table in the DB. It is kind of a mess with talking to the db, then processing stuff, then talking to the DB again for the rest.

I am trying to create a script which could be run on a cron schedule which would take the data from the existing system, process it, and put it into a different database with a normalized schema.

I have initally created a PHP script which does this, but for just one category (out of ~800) containing only 350 products (out of 100k) the script takes about 5 minutes to complete. I need something quicker. My script uses ADODB library for interaction with mysql.

Here are some options (since the format is question/answer), and I'm curious if one of these might be exponentially better in performance:

Upvotes: 1

Views: 573

Answers (1)

Tim3880
Tim3880

Reputation: 2583

I think you may want to write some "translating" procedures in mysql itself to process old data and generate new entries directly. It can be faster than other solutions.

If query performance is not an issue, you can just create some views (procedures, functions) based on the current schema and make your further programming easier. Some materialized views can actually help improve performance.

If you have to "transform" a huge database outside MySQL, you can write your "results" to disk files first (text or sql) and import them to MySQL after all your "data" are transformed.

Upvotes: 1

Related Questions