WP ULike Database Optimization Suggestions

First of all, thank you for your great work on developing and maintaining WP ULike.

I would like to share some suggestions regarding database performance and storage efficiency, particularly for high-traffic websites.

Our ulike_pulse table has currently grown to over 5 million rows, occupying nearly 3 GB of storage. After analyzing the table structure, it appears that a significant portion of this size comes from repeated long text values and anti-spam metadata that is stored indefinitely for every engagement.

To improve scalability and database efficiency, I would like to suggest the following optimizations for future versions:1. Data Retention Policy for Non-Essential Metadata

Fields such as fingerprint, dedupe_token, browser, and os are primarily useful for anti-spam and security purposes at the time of engagement.

After a certain period, such as 30, 60, or 90 days, this information may no longer be operationally necessary.

Suggestion: Add an option in the plugin settings to automatically clear or purge non-essential metadata from records older than a configurable number of days, while keeping the core engagement record intact.2. Optimize Column Data Types

Columns with a limited number of possible values could use more storage-efficient data types such as TINYINT or ENUM instead of longer strings.

For example, fields such as device, status, and engagement_kind could potentially be represented using compact numeric values.3. Optimize Like/Dislike Values with Numeric/Boolean Types

Currently, values such as engagement_key may store full strings such as 'like' or 'dislike'.

Instead, these values could be represented using a compact numeric type such as TINYINT, for example:

• 1 = Like
• 0 or -1 = Dislike

This would reduce the storage required for each value to approximately 1 byte, compared with several bytes for the corresponding string representation.

Across millions of records, this relatively simple change could substantially reduce the overall table size as well as the size of related indexes.4. Store IP Addresses More Efficiently

If IP addresses are currently stored as text (VARCHAR), they could potentially be stored using INT UNSIGNED for IPv4 addresses, with INET_ATON() and INET_NTOA() for conversion.

This requires only 4 bytes per IPv4 address, reducing storage compared with text-based representation.5. Normalize Browser and Operating System Data

Storing complete browser and operating system strings repeatedly across millions of records can create significant database overhead.

Suggestion: Store unique browser/OS combinations in a separate lookup table, such as ulike_user_agents, and reference them from the main table using a compact integer user_agent_id.6. Separate Core Engagement Data from Security/Audit Data

Another possible optimization would be to separate essential engagement data from heavier anti-spam and security information.

For example:

Core table (ulike_engagements):

• id
• user_id
• item_id
• item_type
• date_time
• engagement_type

Audit/security table (ulike_engagement_logs):

• fingerprint
• dedupe_token
• ip
• browser
• other anti-spam metadata

This would keep the primary engagement table lightweight and make the most frequently used queries and indexes more efficient.7. Built-in Archiving and Maintenance Tools

It would also be useful to provide a WP-CLI command or background cron job that allows administrators to archive or clean up historical engagement data in bulk.

These database optimizations could significantly reduce storage requirements, index sizes, memory usage, and query overhead on high-traffic WordPress websites.

I hope these suggestions can be considered for future versions of WP ULike. They could be particularly beneficial for websites with millions of engagement records.

Thank you very much for your time and for your continued work on the plugin.