4

I'm using MySQL, normalized table for all transaction.

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_date 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 do 1 of this ?

1. Create View in MySQL 
**OR**
2. Denormalize data (Create 1 more table for order_log), but I think it would
cost so many storage size
**OR**
3. Using SSD ?

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 because the data size).

Any solution ? Thank you.

Zinc
  • 385
  • 1
  • 5
  • 19
  • `ORDER BY order_id DESC LIMIT 30` can be tricky to get optimized right because MySQL optimizer could choose to access the tables in a different order and needs to use the quicksort algoritme again to order the results. And because of the large amount of records MySQL optimizer most likly needs to use a temporary table.. – Raymond Nijland Apr 19 '18 at 12:53
  • Really sorry, `ORDER BY order_date DESC LIMIT 30` is the correct query. – Zinc Apr 19 '18 at 12:54
  • Shouldn't that be ```LIMIT 0,30```, then ```LIMIT 30,30```, and so on for each page? – Sloan Thrasher Apr 19 '18 at 13:17
  • Without the full query, it's hard to say if there might be some optimizations that could be used. For instance, one or more of the tables might be joined as a subquery with conditions that reduce the number of rows involved in the main query. Creating a view simplifies your query as seen by the programmer, but is still a query that the server has to run, so it doesn't help performance. – Sloan Thrasher Apr 19 '18 at 13:20

1 Answers1

2

I depends.

  • A VIEW is just syntactic sugar. It provides no extra performance. However, it may make your SQL easier to read.

  • Denationalization may help. Reformulating may help. More importantly, focusing first in finding the 30 rows is likely to help. Then deal with the rest of the JOINs. (I cannot be more specific without seeing the query, including which columns are in which tables.)

  • SSDs will help if you are I/O bound. With thousands of rows, you are very unlikely to be I/O bound. A million rows may see a difference. (Need more info to elaborate.)

  • Do not normalize "continuous" values, such as FLOATs or DATETIMEs. It is very likely to make 'range' tests inefficient. More discussion.

  • Please provide SHOW CREATE TABLE, EXPLAIN SELECT ...

  • ENUMs are sometimes a good way to save space and avoid normalization.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Can you please elaborate the point of not normalizing the continuous data, because [I can't understand it](https://stackoverflow.com/questions/49751952/how-do-i-not-normalize-continuous-data-ints-floats-datetime) and I believe this is a very important point. Thank you Mr.Rick – Accountant م Apr 27 '18 at 14:45
  • 1
    @Accountantم - I have [_responded_](https://stackoverflow.com/a/50066912/1766831). If it is still not clear, maybe the discussion there improve it. I did not see that Question because I troll only certain [tags]. (Rick's RoTs is meant to be one-liners; no room for elaboration.) – Rick James Apr 27 '18 at 16:49
  • Thank you, BTW I [notified](https://stackoverflow.com/questions/49723844/join-2-tables-on-dynamically-changing-column#comment86518492_49723844) you about this question the day I asked it, but it looks like you didn't notice, I'm sorry for that – Accountant م Apr 27 '18 at 17:02
  • @Accountantم - I've been tied up for a few months; I guess it slipped thru the cracks. I'm happy to elaborate on whatever you like. – Rick James Apr 27 '18 at 17:23
  • Hi, could you provide more examples ? thank you very much @RickJames. Ending in 19 hours – Zinc Apr 29 '18 at 06:08
  • @Zinc - start with a subquery like `( SELECT id ... ORDER BY .. LIMIT 30 )` If we can optimize that to use an index and reference only 30 rows, performance will be benefited. – Rick James Apr 29 '18 at 16:18