This kind of query..which kind of MYSQL type?

Discussion in 'MySQL' started by glasglow, Jul 12, 2007.

0
  1. #1
    I am currently using a database MYISAM and when I run these queries on a database with 111 tables.. and I'm calling each table usually 1 at a time. The problem is .. if one table is very long.. currently 17,000 rows.. it doesn't allow any of the columns to be processed without timing out. Any better way? I was thinking it was an index, or table type problem.

    mysql_connect("localhost","name","pass");
    mysql_select_db("database");

    // Get data records from table.
    //$result1=mysql_query("SELECT column FROM table ORDER BY RAND() LIMIT 1");
    $result2=mysql_query("SELECT column FROM table");
    //$row=mysql_fetch_array($result1);

    $string = $_POST['data'];

    while($row2=mysql_fetch_array($result2)){
    $result1=mysql_query("SELECT column FROM table ORDER BY RAND() LIMIT 1");
    $row=mysql_fetch_array($result1);
    blah blah stuff
    }
     
    glasglow, Jul 12, 2007 IP
  2. Synch

    Synch Peon

    Messages:
    76
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Thats a lot of rows to be bringing back at one time. You could do a number of things...

    1. run a loop where the data is brought back in 1000 row chunks

    2. partition the table on the id and create views over it, so that you wont lock the whole table for a long period.

    3. Create a more precise query for what is needed.

    4. Try bulk loading if your just moving the data.
     
    Synch, Jul 12, 2007 IP
  3. Synch

    Synch Peon

    Messages:
    76
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I just looked again, Are you trying to just get 1 random row out of 17k? If thats the case just change that first quesry to use random limits made by php, so that it would be something like LIMIT 34453, 1 or LIMIT 234,1 etc... That way you take the strain off the db.
     
    Synch, Jul 12, 2007 IP
  4. glasglow

    glasglow Active Member

    Messages:
    926
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #4
    Thank you.. I'll use that on the larger lists... but now I'm going to have to reorder the lists in terms of most important... I took the lists off columns and put them on individual tables which helped out a lot but still getting the timeouts on the longer lists..Anyway I'll do the limit...
     
    glasglow, Jul 12, 2007 IP