0

I have a select where I need to scan a table to get results:

where (p.user in (select f from fl where user =? and block=0))

This table is a big table with more than 1 million rows. And it is taking a while to read. I need to read it all the time, so I was thinking I could read it once and then just use:

where (p.user in ($variable_with_all_results))

I tried:

$stmt = $mysqli->prepare("select f from f1 where user = '1' and block = 0");
$stmt->execute();
$stmt->bind_result($variable_with_all_results);

But I cannot use this variable on the select, the mysql is not recognizing it. Any ideas?

Marcos
  • 1,240
  • 10
  • 20
RGS
  • 4,062
  • 4
  • 31
  • 67
  • https://stackoverflow.com/questions/330268/i-have-an-array-of-integers-how-do-i-use-each-one-in-a-mysql-query-in-php – Felippe Duarte May 09 '18 at 19:41
  • https://stackoverflow.com/questions/17226762/mysqli-bind-param-for-array-of-strings?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Marcos May 09 '18 at 19:43
  • "This table is a big table with more than 1 million rows" - if you think that is at all relevant to the performance you need to learn a little bit about database design. What matters is the number of rows matching your predicates and which of predicates match index prefixes. As to resolving the query against multiple literals - you can't do this with PDO. And regardless of the API, its probably more efficient to put the literals into a temporary table and do a join. – symcbean May 09 '18 at 21:17

2 Answers2

1

You should be able to do what you want like this:

$stmt = $mysqli->prepare("select GROUP_CONCAT(f) from f1 where user = '1' and block = 0");
$stmt->execute();
$stmt->bind_result($variable_with_all_results);
$stmt->fetch();

Note you need to add a GROUP_CONCAT aggregation function around f in the query, this will give you a list like 1,5,6. Also you need to do a fetch on the statement to get the result into your variable.

Then you should be able to make a new query like this:

$sql = "SELECT ...
        WHERE p.user in ($variable_with_all_results)";
Nick
  • 138,499
  • 22
  • 57
  • 95
0

bind_result wont work in that way, you should use bind_param(). But using IN (?) won`t do.

You will need to bind each id separately, some think like this:

$stmt->bind_param("select f from f1 where (p.user in (select f from fl where user = IN (?,?,?,?,?) and block=0))", $id1, $id2,$id3, $id4, $id5);

This answer https://stackoverflow.com/a/17228326/2271198 explain better who bind_param works

Marcos
  • 1,240
  • 10
  • 20