php - Query runs fine on the localhost; but, does not run on the web server - Getting the error (SELECT would examine more than MAX_JOIN_SIZE rows)

I am using multiple joins in my sql queries. They were working fine on my localhost. But, as soon as I uploaded my Php website on my ipage web hosting server. The mysqli queries on my site stopped working.I got the following error:

#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
Copy code

I read few old threads on here which dated back 2009 to 2013; which talked about indexing the columns included in the joins. As a conclusion, I was able to run the queries within the Phpmyadmin. But, It did not helped with the website itself. I was unable to fetch the records through the webpage it self using the mysqli queries.

My query example:

<?php
require_once "../config.php";
$state = htmlspecialchars($_GET['state']);


if($state != ""){
        
     $sql=" SELECT 
            item.*,
            sku.*,
            pricing.*,
            shipping.*
        
        
            FROM sku
            INNER JOIN item
            ON SUBSTRING(sku.item_id,2,8) = SUBSTRING(item.item_id,1,8)
            
            LEFT JOIN pricing
            ON sku.sku_code = pricing.sku_code
                
            LEFT JOIN shipping
            ON sku.sku_code = shipping.sku_code
            
            WHERE item.name = ? AND sku.sku_code = ? AND sku.sku_add = ?;
        
        
        ";
        
            if($stmt = $mysqli->prepare($sql)){
                // Bind variables to the prepared statement as parameters
                $stmt->bind_param("sss", $param_a,$param_b,$param_c);
                
                // Set parameters
                $param_a = $_REQUEST["name"];
                $param_b = $_REQUEST["sku_code"];
                $param_c = $_REQUEST["sku_add"];
                
                
                // Attempt to execute the prepared statement
                if($stmt->execute()){
                    $result = $stmt->get_result();
                    
                    // Check number of rows in the result set
                    if($result->num_rows > 0){
                        // Fetch result rows as an associative array
                        while($row = $result->fetch_array(MYSQLI_ASSOC)){
                 
                                
                                $name = $row['name'];
                                $size = $row['size'];
                                $color = $row['color'];
                                $price = $row['price'];
                                /*and so on...*/
                                
                        }
                    } else{
                        
                        exit;
                    }
                }
             
            // Close statement
            $stmt->close();
            }
        // Close connection
        $mysqli->close();
        }
?>

           <input name="name" id="name" type="text" value="<?php echo $name; ?>">
           <input name="size" id="size" type="text" value="<?php echo $size; ?>">
           <input name="color" id="color" type="text" value="<?php echo $color; ?>">
           
           <!--and so on...-->
Copy code

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.