MySQL – Log data with Inner Join 12 Tables or Create View

MySQLoptimizationperformancequery-performance

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.

Best Answer

Normalize, but don't over-normalize. Do not normalize things where the id is not significantly smaller than the value. Do not normalize 'continuous' datatypes (ints, floats, dates); it severely hampers use of 'range' filtering.

Why do you have LEFT? (If you need it, use it; if you don't then remove it so the Optimizer has more options.)

Do have INDEX order_id and do not put that table after LEFT. Then, the Index can be used to find the rows more quickly than via a table scan.

When you still have performance problems, start with a minimal SELECT that finds the 30 ids that you need. Then join to the rest of the tables. (And possibly self-joins back to the table to get the other columns needed.) Use this as a "derived table" (... FROM ( SELECT ... ) ...).

A VIEW is just syntactic sugar. Use it if it simplifies your code, but don't expect any performance benefit.

Related Question