Reading Large CSV Files in PHP Without Timing Out
Attempting to process a CSV file containing 25,000 records row-by-row while simultaneously querying a database often results in the exact symptoms you are seeing: a "Service Unavailable" error triggered by exceeded execution time or memory limits. The core issue isn't just that your code is slow; it's that the architecture treats a batch operation as if it were a single-user request, leading to resource exhaustion on the web server.
Your current implementation loads the entire CSV into memory before processing, then iterates through every record to query MySQL. This approach creates two critical bottlenecks. First, loading 25,000 rows into an array ($line_of_text) consumes significant RAM. Second, executing thousands of individual SQL queries within a single HTTP request forces the database server and PHP process to handle massive overhead without any optimization. When combined with max_execution_time settings, this inevitably triggers timeouts.
Stream Processing vs. Loading into Memory
The most immediate fix is to stop loading the entire file into memory. Your function readCSV attempts to return a complete array of lines, but it does so by appending every line to $line_of_text. With 25,000 records, this creates a large data structure that PHP must manage in RAM before any processing begins.
Instead, use a generator or process the file handle directly without storing the data. By reading line-by-line inside the loop, you keep memory usage constant regardless of file size. Here is how to refactor the reading logic:
function streamCSV($csvFile) {
$handle = fopen($csvFile, 'r');
if (!$handle) {
throw new Exception("Could not open CSV file");
}
// Skip header row if present (adjust based on your actual file structure)
fgetcsv($handle);
while (($data = fgetcsv($handle, 1024)) !== false) {
yield $data;
}
fclose($handle);
}
This approach yields one row at a time, ensuring that only the current line resides in memory. This is essential for handling files larger than available RAM.
Optimizing Database Queries
The second major bottleneck is the query execution loop. Your code executes a SELECT statement for every single email address found in the CSV. For 25,000 records, this means 25,000 database round-trips. This latency compounds quickly, and the cumulative execution time often exceeds the server's timeout limit (e.g., 60 or 180 seconds).
To resolve this efficiently, you should leverage a SQL NOT EXISTS subquery or a LEFT JOIN. These constructs allow the database engine to perform the comparison in a single query rather than thousands. This reduces the operation from O(n*m) complexity to something much closer to O(n + m), where n is the CSV rows and m is the table rows.
// Assuming $csvFile contains 25,000 records
$handle = fopen($csvFile, 'r');
fgetcsv($handle); // Skip header
while (($row = fgetcsv($handle)) !== false) {
$user_email = $row[1]; // Adjust index based on your CSV structure
// Prepare a single query that checks for existence
$stmt = $pdo->prepare("SELECT user_id FROM tbl_user WHERE user_email_id = ?");
// Execute inside loop is still okay if using prepared statements,
// but batching is better if the DB supports it.
// However, for simple existence checks, a single optimized query per row
// with proper indexing on user_email_id is often sufficient.
$stmt->execute([$user_email]);
if (!$stmt->fetch()) {
// Record does not exist in database
echo "Record not exist: $user_email\n";
// Optional: Collect IDs to insert later instead of echoing immediately
// to reduce I/O overhead.
}
}
fclose($handle);
Batching and Indexing
If your goal is strictly to list records that do not exist, consider batching the non-existent emails into a single bulk insert operation if you plan to populate them later. Furthermore, ensure that user_email_id is indexed in your MySQL table. Without an index, every query forces a full table scan, which drastically increases execution time and contributes to timeouts.
Finally, be aware of PHP configuration limits. Even with streaming, very large CSV files can hit memory_limit. While you set it to 512M, the default is often lower (64M). If memory issues persist despite streaming, ensure your web server (Apache/Nginx) allows sufficient time for long-running scripts and that post_max_size isn't inadvertently interfering with large uploads if parsing via POST.
For high-volume data processing in modern PHP stacks, frameworks like Laravel provide tools like Queue to handle these heavy jobs asynchronously, decoupling the file processing from the user request lifecycle. This prevents "Service Unavailable" errors by moving the work off the main web thread.