Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I am experimenting some very disturbing problem using a custom SQL query. I have a custom MySQL table called trading_datas having 165 lines for 4,4Mo. One field datas of my table is a big serialized array.

When requesting over it that way:

$querystr = "SELECT item, datas FROM trading_datas";
$result = $wpdb->get_results( $querystr, 'ARRAY_A' );

my PHP script stops and nothing gets echoed right after that line. When retrieving only item everything works great.

Should I zip my serialized array before putting it in database? Any other solution?

Thanks a lot for help!

share|improve this question
    
Side note: I do advise to use a the table prefix –  janw Jun 8 '12 at 12:07
    
Any reason since its only for personnal purpose? –  Kaaviar Jun 8 '12 at 13:22
    
If it's a WP site you should follow the WP standards, there is no reason not to do it. –  janw Jun 8 '12 at 17:14
add comment

1 Answer

up vote 3 down vote accepted

$wpdb is not 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 );
}
share|improve this answer
    
Thanks for answering. I'll give it a try and valid your answer as soon as I'll have tested it –  Kaaviar Jun 8 '12 at 13:20
1  
Worked like a charm! Thanks again –  Kaaviar Jun 8 '12 at 14:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.