Fix Duplicate Records and Table Bloat

Overview

This guide addresses a common issue in Ultimate POS where the transaction_sell_lines_purchase_lines table develops duplicate records and becomes bloated, consuming excessive disk space. This typically happens due to data synchronization issues or improper cleanup processes.

Table showing excessive size before optimization

Table size successfully reduced after applying the fix

Symptoms

• Large table size (several GB) with relatively few records
• Duplicate entries in transaction data
• Poor database performance
• Disk space issues

Prerequisites

Backup Required

Always create a database backup before performing these operations!

• Database administrator access
• phpMyAdmin or MySQL command line access
• Maintenance window (operations will lock the table)

Solution

Step 1: Verify the Issue

First, check if you have duplicate records:

SELECT stock_adjustment_line_id, purchase_line_id, quantity, qty_returned, created_at, updated_at, COUNT(*) as duplicate_count
FROM transaction_sell_lines_purchase_lines
GROUP BY stock_adjustment_line_id, purchase_line_id, quantity, qty_returned, created_at, updated_at
HAVING COUNT(*) > 1;

Loading...