get_results on large datasets

$wpdb doesn’t suit for fetching huge amount of data from database. Why? In your case:

  1. $wpdb->get_results( ... ) – fetches all results into your RAM at once. It means if you have 4mb, 10mb, or 50mb of data in db, everything will be stored in memory (what is limited as you know).
  2. $wpdb->get_results( ..., ARRAY_A ) – $wpdb fetches everything as object by default, and then if you pass ARRAY_A, it will convert array of objects into array of associated arrays. It means that if you’ve already fetched 4mb/10mb/50mb of data into memory, you have to multiply it 2 or even more times.

So, I suppose your issue is in reaching memory limit by your script. That’s why it works for one result and doesn’t work for all results. There is one workaround for this issue – use mysql_* function in the way you need it:

$result = @mysql_query( "SELECT item, datas FROM trading_datas", $wpdb->dbh );
if ( $result ) {
    while ( ( $row = @mysql_fetch_array( $result, MYSQL_ASSOC ) ) ) {
        // do stuff here for each result ...
    }
    @mysql_free_result( $result );
}

Related Posts:

  1. How to use $wpdb to delete in a custom table
  2. Is there a (better) way to access $wpdb results?
  3. Using $wpdb generates DB error
  4. How do you use prepare when asking for a list of id’s
  5. how to execute different sql query in non-sanitized $wpdb->get_results function
  6. WPDB update row with != in where clause
  7. How to update records using $wpdb?
  8. Need help writing a $wpdb query
  9. show badge with count for pending items in custom post type
  10. WP Sql query multiple where clause
  11. WordPress SQL query – returning ‘true’ ‘false’ or ‘null’
  12. Modify the structure of data returned by $wpdb
  13. Syntax for $wpdb->prepare when searching in two columns
  14. Confused by $wpdb->prepare
  15. How to display user_nicename and usermeta values by custom query in WordPress?
  16. Optimizing WordPress Queries – Removing Group By ID
  17. How can I combine one field using wpdb and group by?
  18. $wpdb->prepare with ON DUPLICATE KEY UPDATE
  19. how to use $wpdb->prepare to update a custom table
  20. WPDB Placeholders and second argument for prepared statements
  21. Increment integer field in database when WHERE needs to be dynamic [closed]
  22. Custom SQL query ORDER BY term_order
  23. Custom $wpdb returns unexpected time based results
  24. How to left join meta in queries [closed]
  25. wpdb->get_row is selecting the variable as a column name
  26. CREATE TABLE with dbDelta does not create table
  27. $wpdb query outputs php code instead of executing it
  28. wpdb query not working
  29. WordPress wpdb->insert returns int(0) => doesn’t insert anything, no errors!
  30. WPDB SQL Ignore `post_status` Parameter
  31. how to list all post that are in the custom taxonomy using $wpdb
  32. WPDB SQL query SELECT from category
  33. How to use WHERE NOT EXISTS query to avoid duplicate entry using $wpdb to save in custom table?
  34. Creating an Angular factory from custom database table
  35. wpdb->update update the entire table instead of one row
  36. How do I update post based on meta_key in another table?
  37. How to set up prepared query using IN statement
  38. Custom database query to validate data
  39. Alter the main search query to search posts by coauthor user name
  40. Creates only one table and not the other
  41. Protect custom form from SQL injection
  42. looking for a way to allow users to backup the plugin db data(save as)
  43. SQL Query to select post title & post ID from a particular category
  44. Get comments after specific date
  45. query using wpdb in wordpress gets me no result
  46. Get count of rows based if column exists in two different tables
  47. query_vars doesn’t return query string (trying to get data from $wpdb)
  48. Matching database content types to PHP types
  49. Use wpdb->prepare for `order by` column name
  50. wpdb get posts by taxonomy SQL
  51. Display data from a non wordpress database on a page template
  52. How to make an activities stream mixing posts and comments?
  53. Get data from database table by post_id to get data from second database table
  54. Getting users who registered 360 days from current date
  55. Querying Email Addresses for a List of Users with Same Last Name?
  56. $wpdb sql help. Select post id and post meta value based on 2 other post_meta values
  57. $wpdb prepared with search term
  58. Really simple query giving error in SQL syntax
  59. What does wp_update_post() do that the $wpdb class does not?
  60. I want to update my postcontent with $wpdb
  61. $wpdb->replace / Replace or update primary key
  62. Create an array with a string key from wpdb->get_results
  63. Replicating the WP_Query ‘s’ param with $wpdb
  64. get the number of queries made on a page(inside my plugin)
  65. Doing a loop with multiple DBs simultaneoulsy
  66. WPDB If primary key already exists, add +1 to integer field
  67. How to get an array of user roles with or without a specific capability?
  68. Programmatically Creating Page using $wpdb and getting 404 error
  69. Create table from array with prepare
  70. 301 Redirect Code
  71. wpdb query problem to access previous 3 days posts
  72. $wpdb->insert inserting only f character in custom table
  73. PHP Fatal Error – $wpdb a non-object?
  74. Why does wpdb->update delete other meta?
  75. Make a SQL query with wpdb in WordPress
  76. Getting variable from Database
  77. PHP -> SQL Query with Summing
  78. How to delete a particular row in a database table
  79. Get emails from users by custom field SQL
  80. Save sql file after doing insert wpdb
  81. Can I use wpdb to insert query results into a post?
  82. Getting value from database table depending on field value
  83. $wpdb->prepare error after WordPress update [duplicate]
  84. How to pass an input value into wpdb->Prepare
  85. WordPress SQL Prepare
  86. wpdb replace returning 1 where delete and insert is expected
  87. update_post_meta not working in template_redirect action
  88. How to work with constraints on wordpress user metadata?
  89. Help posting values to DB on submit using $wpdb->query
  90. Get 3 row ID’s via ARRAY_A
  91. Getting record from three wpdb tables
  92. Why is an empty result an error? ( $wpdb->get_row )
  93. get_row returns empty when data exists
  94. wpdb insert working in one function, but not another
  95. wpdb select from using array as search parameters
  96. Can’t get expected result from a wpdb query
  97. selecting row using wpdb which contain special symbols
  98. Query Problem in Clustom Plugin
  99. Table wont load into WPDB
  100. WPDB prepare – like % – placeholders?

Leave a Comment