1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Can I speed up this query?

Discussion in 'MySQL' started by mahmood, Apr 3, 2006.

  1. #1
    Hi

    I am trying to read the contents of a mysql table into an array. The problem is that the table is very huge and it takes a long time. Does anybody have any suggestion to reduce the time.

    Database includes 200K rows and 42MB

    The code that I use - it read the table into a multidimentional array -

    	
    $sql = "SELECT * FROM myTable";
    
    $result = mysql_query($sql) or die(mysql_error());
    while ($result_array = mysql_fetch_assoc($result)){
    	while (list($field, $value) = each($result_array)){
    		$value = stripslashes($value);
    		$value = str_replace("\n","<br/>",$value);
    		$type[$field][] = $value;	
    
    	}	
    }
    PHP:
    .
     
    mahmood, Apr 3, 2006 IP
  2. Sarangan

    Sarangan Well-Known Member

    Messages:
    127
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    108
    #2
    Do you use phpmyadmin? or what?
     
    Sarangan, Apr 3, 2006 IP
  3. mahmood

    mahmood Guest

    Messages:
    1,228
    Likes Received:
    43
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Hum..., I use phpmyadmin but I don't think it has got anything to do with this code.
     
    mahmood, Apr 3, 2006 IP
  4. woodside

    woodside Peon

    Messages:
    182
    Likes Received:
    8
    Best Answers:
    0
    Trophy Points:
    0
    #4
    How long does the actual query take? I would guess that it is taking a lot longer to populate the array than for the actual query. Just curious, why are you trying to make a php array with 200k rows? There might be an easier way to do whatever it is you are trying to do.
     
    woodside, Apr 3, 2006 IP
  5. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #5
    You can speed up the query by adding SQL_BIG_RESULT and SQL_CACHE to the query
    SELECT SQL_BIG_RESULT SQL_CACHE * FROM myTable
    Code (markup):
    SQL_BIG_RESULT tells the optimizer to get ready for a big result set and SQL_CACHE saves the result set (This will help for subsequent queries, where the table hasn't been modified.)

    BUT
    The query is the quick part of your script. I'd suspect the php is what's making it display slowly. You can increase memory for php (use ini_set to change the memory_limit config option), and maybe you can optimize your while loop. I'd try losing the call to list and instead directly use the array, and you could try nl2br instead of str_replace and see if it's faster.
     
    exam, Apr 3, 2006 IP
  6. rossriley

    rossriley Guest

    Messages:
    25
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Hello.

    Why do you need to select 200k rows? Surely you can't be using all this on one page.

    If you do then you probably need to think about adjusting your application to only process a few rows at a time. If you can give more detail about what you're doing then we may be able to help more.
     
    rossriley, Apr 3, 2006 IP
  7. exam

    exam Peon

    Messages:
    2,434
    Likes Received:
    120
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Yes, if you're just outputting to the browser, (there's no need to manipulate the array later) then it would be better to just dump it out instead of saving it into an array.
     
    exam, Apr 3, 2006 IP