I'm using MySQL (phpmyadmin) with engine InnoDB for my e-commerce website.
Now, I need to get the data as Log but It's need to joining more than 12 tables for logging all data we need to see, only last 30 data submitted (done / reject) will show in pagination (Log page 1) but still have pagination for next last 30.
9 INNER JOIN
3 LEFT JOIN (It's Admin Order Form when customer Ordering via Offline (friend))
WHERE order_status = 1 OR order_status = 2 (done & reject)
ORDER BY order_id DESC
LIMIT 30 (limiting 30 per page with pagination)
The order data is more than 2.000.000 rows for 4 tables in INNER JOIN connected in PK & FK.
Any problem if I'm joining 12 tables
OR
I should create table view or something ?
Sometime we need to make sure storage is saved with normalization, sometime we need the performance faster. (but still not wanted to use denormalization data for log).
Any solution ? Thank you.