database - How to improve the performance when using a flat-file dataset and PHP for implementing advanced search function?

admin管理员组

文章数量:1122846

I have to add some advanced search function to a WordPress website, I can't use SQLite database because we are not allowed to install any driver on the system. I solved the issue using a CSV file that I open using a .php file in the htdocs however now I am running out of PHP memory. I am using fgetcsv to read the CSV so it should not read the whole CSV file in memory, but I am wondering what strategy can I use to improve the WordPress website PHP memory consumption and if there is a way to understand how much memory my advanced search functions based on a PHP file is using? Or detect any memory leak from wordpress?

I have to add some advanced search function to a WordPress website, I can't use SQLite database because we are not allowed to install any driver on the system. I solved the issue using a CSV file that I open using a .php file in the htdocs however now I am running out of PHP memory. I am using fgetcsv to read the CSV so it should not read the whole CSV file in memory, but I am wondering what strategy can I use to improve the WordPress website PHP memory consumption and if there is a way to understand how much memory my advanced search functions based on a PHP file is using? Or detect any memory leak from wordpress?

• php
• database
• search
• memory

Share Improve this question edited Jun 4, 2024 at 9:56 G M asked May 22, 2024 at 10:42 G MG M 4955 bronze badges 2

• can you explain what you mean by a driver? Also can you make this more specific/concrete and a single question? This looks like multiple questions bundled into 1 on improving performance, reducing memory, detecting memory usage, predicting memory usage etc. You need to be able to mark an answer as the factually correct answer for all people with this question, it can't be asking for general advice or recommendations. Also if SQLite is not an option is there a reason you didn't opt for the MySQL already installed? – Tom J Nowell ♦ Commented Jun 10, 2024 at 15:38
• @TomJNowell sorry I could not make it more precise what I was looking was an answer on the general optimization of this solution. I thought that using MySQL could provide more attack surface. And hence I preferred to separate the main database from the application. – G M Commented Jun 13, 2024 at 15:14

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

Query Monitor is a good way to track what part of WP is chewing up resources. What search functionality are you trying to implement, exactly? It's very hard to give advice based on a vague question that leaves out relevant details.

Step-by-Step Instructions for Improving Performance with a Flat-File Dataset in PHP

1. Define the Function to Read and Process the CSV File in Chunks: Create a function that reads the CSV file line by line to avoid loading the entire file into memory. Use fgetcsv to read each line and search within it.

2. Add Memory Usage Measurement: Use memory_get_usage to measure memory usage before and after the search operation. This helps in understanding how much memory your script is consuming.

3. Adjust PHP Memory Limit: Increase the PHP memory limit in your wp-config.php file if necessary.

4. Example Usage: Implement an example usage of the search function to demonstrate how it works in a real scenario.

Here’s the complete code to achieve the above steps:

<?php

// Function to read and process CSV in chunks
function search_csv( $file_path, $search_term ) {
    $handle = fopen( $file_path, 'r' );
    if ( $handle === false ) {
        return [];
    }

    $results = [];
    while ( ( $row = fgetcsv( $handle, 1000, ',' ) ) !== false ) {
        // Add your search logic here
        if ( strpos( strtolower( implode( ' ', $row ) ), strtolower( $search_term ) ) !== false ) {
            $results[] = $row;
        }
    }

    fclose( $handle );
    return $results;
}

// Function to measure memory usage
function memory_usage() {
    echo 'Memory usage: ' . memory_get_usage() . ' bytes' . PHP_EOL;
}

// Adjust PHP Memory Limit in wp-config.php
// Add the following line to wp-config.php
// define( 'WP_MEMORY_LIMIT', '256M' );

// Example usage
$file_path = 'path/to/your/file.csv';
$search_term = 'your_search_term';

memory_usage(); // Measure memory usage before the search
$results = search_csv( $file_path, $search_term );
memory_usage(); // Measure memory usage after the search

// Output results
foreach ( $results as $result ) {
    echo implode( ',', $result ) . "\n";
}

Additional Steps

1. Adjust PHP Memory Limit: In your wp-config.php file, add the following line to increase the PHP memory limit:

   define( 'WP_MEMORY_LIMIT', '256M' );
   

2. Ensure Efficient Search Logic: Make sure your search logic is efficient to minimize memory and CPU usage. The example provided uses strpos for string matching, which is generally fast.

3. Utilize WordPress Debugging Tools: Use WordPress debugging tools like Query Monitor or New Relic to identify memory leaks or performance issues.

4. Profile and Optimize: Continuously profile your script’s performance and optimize as needed. This might involve further refining the chunk size for fgetcsv or optimizing the search logic.

本文标签：