0

I am using MySQLi Procedural to run query from a MYSQL database. I have only seen how this is done using MySQLi Object-Oriented code.

It runs perfectly on localhost, but on the remote server I get an error message saying that I have more than MAX_JOIN_SIZE rows. What code do I use to fix this error?

I have looked at all the pages concerned with this error and although I know what the problem is, I can't work out how to fix it. All of the examples I have seen use MySQLi Object-Oriented code. I have tried limiting (LIMIT 30) the number of records returned, but this made no difference.

<?php
$con=mysqli_connect("localhost","******","******","ps10");
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$query_rs4 = "SELECT * FROM  student_data INNER JOIN users ON student_data.class = users.class INNER JOIN ext_writing_tbl ON student_data.id = ext_writing_tbl.id";

$rs4 = mysqli_query($con, $query_rs4)or die( mysqli_error($con) );
$row = mysqli_num_rows($rs4);
echo "There are " . $row . " students in this class";
?>

This is the complete error message.

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.

It should output to a table.

John Twiname
  • 47
  • 5
  • Are you allowed to change your database config options? – bassxzero May 29 '19 at 12:44
  • Possible duplicate of [MySQL: "The SELECT would examine more than MAX\_JOIN\_SIZE rows"](https://stackoverflow.com/questions/943423/mysql-the-select-would-examine-more-than-max-join-size-rows) – Your Common Sense May 29 '19 at 12:54
  • Run. Just run from this host as fast as you can. It is oversold beyond any imagination and your problems only started here – Your Common Sense May 29 '19 at 12:55
  • most likely indexes would out help here.. post table structures `SHOW CREATE TABLE table` and a `EXPLAIN query` .. but it seams you are selecting a possible large resultset.. – Raymond Nijland May 29 '19 at 14:31
  • There are about 1200 records. If I limit the number of records, it does not work. Problem is that I have joined 3 tables. – John Twiname May 29 '19 at 14:45
  • Thanks Your Common Sense - I have seen this post. There is no way of knowing if the suggestion was accepted. And it uses MySQLi Object-Oriented code, not Procedural. – John Twiname May 29 '19 at 14:48
  • It is certainly a JOIN issue - if I remove the third table from the query it runs (but I can't do what I want as I want as the third table is now missing). Surely this is a server hosting issue. – John Twiname May 29 '19 at 15:01

1 Answers1

0

I am sure now that this is something set on the server and is beyond my control. How then to write a query that does not use three tables (uses INNER JOIN twice)?

John Twiname
  • 47
  • 5