I would lean toward a single Category_Properties table with product_id, category_id, and optionally (if you need to retain the position of the property values in the original design) a sequence number of some sort. You are going to have to do some programming to convert this, but it can be done with some 'brute force' SQL if you are trying to avoid scripting something.
CREATE TABLE Category_Properties
( product_id
, category_id
, sequence_value /* this is optional */
, property_id
);
INSERT INTO Category_Properties
SELECT * FROM (
SELECT product_id, 43 AS cat, 1 AS seq, <first_property_col> AS pval FROM category_properties_43
UNION
SELECT product_id, 43, 2, <second_property_col> FROM category_properties_43
UNION
SELECT product_id, 43, 3, <third_property_col> FROM category_properties_43
)
WHERE pval IS NOT NULL
;
-- From your example this would be <first_property_col> = 17, then 21, then 56.
You can do this provided you know the max number of property columns for each category, the outer query will eliminate the missing values for products which don't use all the property columns. You can see why you might want to script this, rather than changing it for each new category.
Best Answer
You could enable binary logging. If you have not done so already.
To find out if binary logging is enabled, run this:
If you get back,
Empty set, it is not enabled.If you have not, please add this to
my.cnfunder the[mysqld]sectionThen, restart mysql.
From that point forward, you could look for all changes within timeframes.
For example, suppose you run
SHOW BINARY LOGS;and get something like this:You could then use the mysqlbinlog utility to dump a text representation of all the SQL commands executed within specified timeframes along with the timestamps.
The options to use are
What to do next
Go find the folder where the binary logs are kept. From the command line run this
The datetime stamp for each file represents the last time the binlog received SQL commands.
You would request the commands by the timeframe and by the binlog names ordered by timestamp
To see all SQL commands executed from midnight to 8:00AM today, you run this
The output file,
/tmp/SQLCommands.txt, will contain everyINSERT,UPDATE,DELETE, andDDLcommand executed in the timeframe you requested. Just make sure you supply the necessary binlogs. If you include all binlogs, the mysqlbinlog dump will still work but would take longer because it would parse through binary logs it does not need (i.e., you don't need to parsebin-log.000645-bin-log.000648,bin-log.000652,bin-log.000653).Give it a Try !!!
CAVEAT
If you want to see everything, including the SELECTs, you go with Craig Efrein's answer.
UPDATE 2014-08-21 10:45 EDT
@CraigEfrein made a excellent point in his comment
To reduce disk I/O based on binary logging, you should config binlogs to be stored on a separate disk. See my old post How do I determine how much data is being written per day through insert, update and delete operations? (under the subheading SSD LIFESPAN)