2

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.

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.