0

I'm trying to figure out how to execute the MySQL statement SET SQL_BIG_SELECTS=1 in a PHP program using PDO but I can't find anything here or via google to show me how to do this. In fact, I'm not sure it can even be done in a PHP program.

I've started getting MySQL error 1104 (Sqlstate=42000) due to a UNION statement that combines two queries in my database. I've tried creating the only index that would help the statement perform better but it hasn't resolved the error.

[Edit: Here is the statement I am trying to execute:

SELECT Story.Type as Story_Type, Story.Author as Story_Author, Story.Title as Story_Title, Book.Format as Book_Format, Book.Type as Book_Type, Book.Title as Book_Title, Book.Author as Book_Author
    FROM `SciFic` Story inner join `SciFic` Book
    on (Story.Anthology_Number = Book.Year_Published and Story.Author = Book.Author)
    Where Story.Type = 'SHORT STORY'

    UNION

    SELECT Story.Type as Story_Type, Story.Author as Story_Author, Story.Title as Story_Title, Book.Format as Book_Format, Book.Type as Book_Type, Book.Title as Book_Title, Book.Author as Book_Author
    FROM `SciFic` Story inner join `SciFic` Book
    on (Story.Anthology_Name = Book.Title)
    Where Story.Type = 'SHORT STORY'

    Order by 3

I created a non-unique index on the type column and it has a cardinality of 18. There are 10982 rows in the table.]

I had hoped that this code would be sufficient:

$db->exec("SET SQL_BIG_SELECTS=1") or die(print_r($db->errorInfo(), true));

but it gives me this instead:

Error encountered while getting SciFic short stories: SQLSTATE[42000]: Syntax error or access violation: 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

I would dearly love to know how to overcome this problem if anyone can suggest something. This code worked fine a few months back but I'm using a hosting service and I think they must have updated their version of MySQL because the data has NOT changed.

Henry
  • 1,395
  • 1
  • 12
  • 31
  • Can you add the SQL statement you're trying to get the results for -- the UNION statement? – hd1 Oct 02 '20 at 03:34
  • https://stackoverflow.com/a/14539536/10955263 suggests that you should try and set `MAX_JOIN_SIZE`, which is also mentioned in the error message you got. – 04FS Oct 02 '20 at 08:02
  • 04FS - The error message specifically says to set SQL_BIG_SELECTS *or* MAX_JOIN_SIZE so I did the former. I would be happy to set both if necessary. I just need someone to explain to me HOW (Where) to do it. In other words, can I do those things in my program or do I have to do them in PHPMYADMIN or a MySQL command prompt? – Henry Oct 02 '20 at 15:00
  • I eventually solved the problem and set both parameters with the following code: try { $db->exec("SET SQL_BIG_SELECTS=1"); $db->exec("SET MAX_JOIN_SIZE=1000000000"); } catch (PDOException $excp) { echo "Error encountered setting SQL_BIG_SELECTS and MAX_JOIN_SIZE: " . $excp->getMessage() . "\n"; } A moderator closed my question as having already been answered elsewhere and I see that solution now but mine is different because I do the SETs inline with exec() statements, which may help someone down the line – Henry Oct 02 '20 at 19:00

0 Answers0