Code variables split Large SQL

Suggestions for improving the slow search for XXX:

1, do not use *, check the table should specify the column name

2, avoid using sub-query

3, when the joint table/step-up query, the scope of the first narrow

4, the operation can be done in the application memory, do not give the database

This is two more urgent to solve, follow-up next time to communicate

One

Select

T.*,

Case C.card_type

When ' Debit ' and then ' xxx '

When ' prepaid ' and then ' xxx '

When ' credits ' Then ' xxx '

When the ' semicredit ' then ' xxx ' END ' card_type ',

C.cardbin_name, C.issuer_name, A.name acquirer_name, M.merchant_name,am.merchant_name

From ((((

Trans_history T

Join Dict_cardbin C on t.cardbin_id = c.id)

Left join Cm_merchant m on T.merchant_id=m.id)

Left join acquirer A on t.acquirer_id = a.id)

Left JOIN acq_merchant am on t.acq_merchant_id = am.id)

Left JOIN Agency AG on m.agency_id = ag.id

ORDER BY t.id Desc

Limit 0, 20;

Trans_history This table is very large, with 5G, the whole table scan cost is also very large. Can the old data be partially divided according to the access requirements of this table?

Several other tables are smaller. However, this query requires only the latest 20 IDs in the Trans_history, and the table action will concatenate the whole table and reorder them. The cost of time required is the trans_history of the whole sweep, which is similar to that of O (m*n*t ...) .

in the Dict_cardbin, Cm_merchant, acquirer, Acq_merchant, agency IDs are primary keys, it is very fast for us to retrieve only 20 records per table. Equivalent to O (m+n+t ...)

It is proposed to read:

First step check the main table, 20 records

sql = Select ID, col1, col2,..., cardbin_id, merchant_id, acquirer_id, acquirer_id, acq_merchant_id from Trans_hi Story ORDER BY id desc limit 20;

Rowset = sql_query (SQL)

Combine the next query with the required ID string

Cardbin_id_str = ', ', Join (str (row.cardbin_id) for row in Rowset)//form "ID1, Id2, id3 ...., id20"

The second step is to check the branch table:

Cardbin_sql = Select ID, card_type, cardbin_name, issuer_name from Dict_cardbin where ID in ($cardbin _id_st)

Cardbin_rowset = Sql_query (cardbin_sql)

In turn

Card_type less, directly placed in the program memory

Card_type_map = {"Debit": "Debit Card", ...}

Final Result:

For row in rowset:

Print Row.id, Row.col1, row.col2 ..., Card_type_map[cardbin_rowset[id].card_type], Cardbin_rowset[id].cardbin_name, Cardbin_rowset[id].issuer_name, Acquirer_rowset[id].name, Cm_merchant_rowset[id].merchant_name, Acq_merchant_ Rowset[id].merchant_name

Two

SELECT temp.id, temp.acq_merchant_id, temp.enabled, Temp.batch_no, Temp.trace_no,temp.terminal_no,temp.merchant_no

From (

SELECT C.*,b.merchant_no,ks.key_alias

From acq_merchant_terminal C

Left JOIN acq_merchant B on b.id=c.acq_merchant_id

JOIN acquirer A on a.id=b.acquirer_id

Left JOIN key_store ks on ks.key_alias = CONCAT (%s,c.terminal_no,%s) OR Ks.key_alias = CONCAT (%s,c.terminal_no,% S

WHERE a.enabled=%n and b.enabled=%n and and a. ' Code ' =%s) Temp

WHERE Temp.key_alias is NULL;

The amount of data in these tables is not too large, can be linked to the table

When we are in the table/step, we should be careful to use the constraints first to narrow the scope of the search, so that the criteria for limiting the number of primary table rows is advanced.

Select C.id, c.acq_merchant_id, c.enabled, C.batch_no c.trace_no, C.terminal_no, B.merchant_no, B.key_alias//note Do not use *, Explicitly list column names

From acq_merchant_terminal C

Left join Acq_merchant B on b.id=c.acq_merchant_id

Join acquirer A on a.id=b.acquirer_id

Left join Key_store KS to Ks.key_alias in (Concat (%s,c.terminal_no,%s))//Why did the original SQL write two times this condition? Note You should try to avoid using or to join the search condition, if there are multiple values, use in (Value list)

where c.enabled=%n and a.enabled=%n and a.code=%s and b.enabled=%n

Mysql> Select COUNT (c.id) from acq_merchant_terminal C left joins Acq_merchant B on b.id=c.acq_merchant_id join acquire R A on a.id=b.acquirer_id left join Key_store ks on Ks.key_alias in ("Acq.ryxpay.88779g648160001.79g60001.zmk") where C.E Nabled=1 and A.enabled=1 and a.code= "Ryxpay" and b.enabled=1;

+-------------+

| Count (c.id) |

+-------------+

| 90617 |

+-------------+

1 row in Set (0.33 sec)

The outer layer retrieves whether the logical Key_alias is null and can be completed by the program:

For row in rowset:

If Row.key_alias! = NULL:

......

--a colleague wrote it.

This article from the "Clear Sky" blog, declined reprint!

Code variables split Large SQL