0

Trying to do very simple thing: 

//connecting to remote host
$db = new mysqli($host, $login, $pass, $dbName) or die('cant connect to db'); 

//table count = 46 644 886 rows
$result = $db->query("SELECT f FROM t") or die($db->error); //no error

echo 'alert!';

The 'alert' text never appears! Later script uses all RAM and system kills it.

Looks like mysqli query tries to fetch all rows in memory. I don't understand that, because only iterator should be returned.

kz_sergey
  • 677
  • 5
  • 19
  • what did `mysqli_error($db)` throw back? hard to say if that query of yours is the actual naming conventions you used here. – Funk Forty Niner Jan 19 '18 at 19:46
  • Why only an iterator? [Read the manual](http://php.net/manual/en/mysqli.query.php) on the `resultmode` option. – tadman Jan 19 '18 at 19:47
  • @FunkFortyNiner `$db->error` in this case. Way less typing to use the proper object-oriented interface. – tadman Jan 19 '18 at 19:48
  • ^ that too............... edit: @tadman yep, once that kicks into gear. – Funk Forty Niner Jan 19 '18 at 19:48
  • what you posted is way too simple. It's all nice that you reduced it to a minimal, but that is way too minimal. Make sure your db's properly indexed. I think the question's too unclear. – Funk Forty Niner Jan 19 '18 at 19:49
  • Yes, only the iterator will be returned but the table has 46 644 886 records as u indicate, so it will return 46 644 886 values, no matter if its just one field, u are still fetching every row because of ur query, it takes time, and with 46 644 886 records...it takes a lot of time. – Francisco Hahn Jan 19 '18 at 19:51
  • resultmode, table and field names doesn't matter. Francisco Hahn: I don't fetch records ( $result->fetch_... ) – kz_sergey Jan 19 '18 at 21:41

1 Answers1

2

1) field and table are reserved names. if you "cleansed" this for posting, it's too clean. Consider posting the actual query and some sample data. Just don't post passwords, IPs, or user names.

2) try the query with a small limit (ie: add "limit 100" to the end of your query). Increase the limit until it crashes. See where the barrier is.

3) I suspect you'll find that there is a barrier either in php or the web server or mysql beyond which virtual memory becomes necessary and the speed of the system drops so low you'll never get an answer.

TheSatinKnight
  • 696
  • 7
  • 16