SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Loops Producing Slow Script Execution

    I'm trying to get my head around the best database design from a speed perspective.

    I've got 3 tables.

    Table A - Stations
    Table B - Songs
    Table C - Station Plays

    Right now I'm procedurally walking through the steps. PS I

    PHP Code:
    // LOOP THROUGH MY STATIONS
    $sql mysql_query("SELECT * FROM stations") or die(mysql_error());
        while(
    $row=mysql_fetch_array($sql)) {
            
            
    // LOOP THROUGH EACH OF THE SONGS TO ENSURE THERE IS AN ENTRY
            
    $sql2 mysql_query("SELECT * FROM songs") or die(mysql_error());
            while(
    $row2=mysql_fetch_array($sql2)) {
                
                
    $sql3 mysql_query("SELECT * FROM tracking_entry WHERE song_id = '".mysql_real_escape_string($row2['id'])."' AND station_id = '".mysql_real_escape_string($row['id'])."' AND chart_week = '".mysql_real_escape_string($currentWeek)."' AND week_string = '".mysql_real_escape_string($week_string)."' LIMIT 1") or die(mysql_error());
                if(
    mysql_num_rows($sql3) != 1) {
                    
                    
    // CREATE AN ENTRY
                    
    mysql_query("INSERT INTO tracking_entry (id, song_id, station_id, chart_week, activity_log, chart_year, week_string, notes, notes_updated) VALUES ('', '".mysql_real_escape_string($row2['id'])."', '".mysql_real_escape_string($row['id'])."', '".mysql_real_escape_string($currentWeek)."', '".mysql_real_escape_string("Added Record On $date\n")."', '".mysql_real_escape_string(date('Y'))."', '".mysql_real_escape_string($week_string)."', '".mysql_real_escape_string($row3['notes'])."', '".mysql_real_escape_string($row3['notes_updated'])."')") or die(mysql_error());
                    
                }
                
            }
            
        } 
    This script is run nightly because it is currently taking about 25 minutes to complete and pretty much bogs down the server. I know there's a better way. One issue is that when I create a new record I have to bring in some data from the last weeks record.

    This may be a really obscure cry for help but perhaps someone can help.

    PS: There are about 14,000 records being produced.

    Thanks
    Last edited by hifigrafix; Dec 4, 2012 at 08:50. Reason: PS

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    This is going to be a one query solution... But real quick, you want to insert a row into 'tracking_entry' where a song_id does not have an entry yet?

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    So what you want to do is make sure that each song is played once on each station each week?

  4. #4
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically there will be ONLY ONE record for each Station + Song combination per week. Sometimes a record gets created outside of this script (ie. manually by a user) otherwise a record is created when this script is run. if we have 20 stations and 20 songs we'll have a total of 400 records (1 per station and song combo) for the current week. Make sense?

  5. #5
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can think of it that way. There will be a record regardless of whether or not the station plays it.

    Thanks

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    So we can write a query for you, we need the table structure of songs and stations please.

    Basically you just want to write a single query to find any song_id and station_id that doesn't exist in the tracking entry for that week. There seems to be a little bit of a deign flaw if your doing this IMO..

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Another question:
    One issue is that when I create a new record I have to bring in some data from the last weeks record.
    I don't see anywhere in your current script where you get that data from last weeks record? You are using $row3 array elements in the INSERT, but SELECT query #3 only gets the current week's data, and you never even do a fetch. So where does that data come from?

  8. #8
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right now I've got the last weeks info importing separately and is not much of an issue at this point. Right now I'd simply like to improve my approach at inserting a row if one with the song_id, station_id, chart_string combination don't currently exist.

    Something like: INSERT IGNORE INTO tracking_entries SET key = val

    I could create a unique key (ie.. song_id+station_id+week_string).

    Would this be a better approach? Perhaps a lot faster than looping in PHP?

    Concerning the bad design comment - I'm sure there's a much better way to do it but this was most simplistic design approach based on some other things I needed to do. If you have a constructive suggestions by all means please suggest them.

  9. #9
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hifigrafix View Post
    Concerning the bad design comment - I'm sure there's a much better way to do it but this was most simplistic design approach based on some other things I needed to do. If you have a constructive suggestions by all means please suggest them.
    Sorry, I didn't mean to be harsh or anything. I don't fully understand the app and what it's doing yet, but from my experience, you probably shouldn't have to add a column to each station daily as a cron job.

    Though if this is indeed necessary, there will definitely be a way to avoid php loops such as this and accomplish it by a single SQL query, once we understand fully what all is going on

    So with that being said, top level, what do these tables do, and what does adding a daily record to the tracking sheet for each station / song id do for you? What is the relationship between all these tables?

  10. #10
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No offense taken. I'm not adding columns - adding rows. Reason this is needed is because I'm needing to access archive data - it's not just a snapshot of "right now".

    Thanks for jumping in here Kyle.

  11. #11
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Just a heads up, record = row.

    So 1 station has many songs and 1 station+1song has many station plays?

  12. #12
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    CREATE TABLE `songs` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `artist_name` varchar(255) DEFAULT NULL,
      `song_title` varchar(255) DEFAULT NULL,
      `label` varchar(255) DEFAULT NULL,
      `notes` text,
      `added` datetime DEFAULT NULL,
      `hide` tinyint(4) DEFAULT NULL,
      `type_mr` varchar(1) DEFAULT 'N',
      `type_ind` varchar(1) DEFAULT 'N',
      `type_ter` varchar(1) DEFAULT 'N',
      `pos_mr` varchar(255) DEFAULT NULL,
      `pos_ind` varchar(255) DEFAULT NULL,
      `pos_bb` varchar(255) DEFAULT NULL,
      `pos_ac` varchar(255) DEFAULT NULL,
      `pos_aa` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1;
    Code:
    CREATE TABLE `stations` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `call_sign` varchar(255) DEFAULT NULL,
      `ind_call_sign` varchar(255) DEFAULT NULL,
      `mr_call_sign` varchar(255) DEFAULT NULL,
      `station_type` varchar(255) DEFAULT NULL,
      `ind` varchar(255) DEFAULT NULL,
      `mr` varchar(255) DEFAULT NULL,
      `tert` varchar(255) DEFAULT NULL,
      `act` varchar(255) DEFAULT NULL,
      `contact` varchar(255) DEFAULT NULL,
      `secondary_contact` varchar(255) DEFAULT NULL,
      `shipping_address` varchar(255) DEFAULT NULL,
      `shipping_city` varchar(255) DEFAULT NULL,
      `shipping_state` varchar(255) DEFAULT NULL,
      `shipping_zip` varchar(255) DEFAULT NULL,
      `mailing_address` varchar(255) DEFAULT NULL,
      `mailing_city` varchar(255) DEFAULT NULL,
      `mailing_state` varchar(255) DEFAULT NULL,
      `mailing_zip` varchar(255) DEFAULT NULL,
      `email` varchar(255) DEFAULT NULL,
      `secondary_email` varchar(255) DEFAULT NULL,
      `phone` varchar(255) DEFAULT NULL,
      `station_owner` varchar(255) DEFAULT NULL,
      `rep` varchar(255) DEFAULT NULL,
      `call_day` varchar(255) DEFAULT NULL,
      `alt_call_day` varchar(255) DEFAULT NULL,
      `old_alt_call_day` varchar(255) DEFAULT NULL,
      `old_call_day` varchar(255) DEFAULT NULL,
      `call_time_start` time DEFAULT NULL,
      `call_time_end` varchar(255) DEFAULT NULL,
      `soundscan_market` varchar(255) DEFAULT NULL,
      `consultant` varchar(255) DEFAULT NULL,
      `website` varchar(255) DEFAULT NULL,
      `start_call` time DEFAULT NULL,
      `notes_updated` timestamp NULL DEFAULT NULL,
      `end_call` time DEFAULT NULL,
      `notes` text,
      `archive` tinyint(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=297 DEFAULT CHARSET=latin1;
    Code:
    CREATE TABLE `tracking_entry` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `song_id` int(11) DEFAULT NULL,
      `station_id` int(11) DEFAULT NULL,
      `chart_week` int(11) DEFAULT NULL,
      `mr_spins` int(11) DEFAULT NULL,
      `mr_finalized` int(11) DEFAULT NULL,
      `mr_position` int(11) DEFAULT NULL,
      `ind_spins` int(11) DEFAULT NULL,
      `ind_position` int(11) DEFAULT NULL,
      `notes` text,
      `notes_updated` datetime DEFAULT NULL,
      `activity_log` text,
      `chart_year` int(11) DEFAULT NULL,
      `mr_spins_updated` datetime DEFAULT NULL,
      `indflag` varchar(255) DEFAULT NULL,
      `ind_spins_updated` datetime DEFAULT NULL,
      `ind_td` int(11) DEFAULT NULL,
      `last_week_mr_spins` int(11) DEFAULT NULL,
      `last_week_ind_pos` int(11) DEFAULT NULL,
      `last_week_ind_spins` int(11) DEFAULT NULL,
      `last_week_mr_pos` int(11) DEFAULT NULL,
      `week_string` int(11) DEFAULT NULL,
      `add` varchar(255) DEFAULT NULL,
      `poss` varchar(255) DEFAULT NULL,
      `last_week_ter_spins` int(11) DEFAULT NULL,
      `ter_spins` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=223817 DEFAULT CHARSET=latin1;
    Every week a new record is fired up for every station + song combination (ie. 20 stations, 20 songs = 400 records for this week). We work on 400 records for the week and then Sunday night the next week rolls over and all new records are created. Problem is - users have the ability to fire up random records before the week rolls over. Because of this I can't just assume that the record needs to be created - thus my original problem. Creating all of the new (blank) records EXCEPT for those already existing for (station X + song X + week X) which may have been manually generated by a user.

  13. #13
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Give this query a try and tell me if it returns all possible combinations (400). artist_name cannot be null for this to work I beleive.

    Code:
    SELECT  so.id, st.id
    FROM songs so
    INNER JOIN stations st ON so.artist_name <> st.id

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hifigrafix View Post
    Because of this I can't just assume that the record needs to be created - thus my original problem. Creating all of the new (blank) records EXCEPT for those already existing for (station X + song X + week X) which may have been manually generated by a user.
    go ahead and create all 400 new rows

    just use INSERT IGNORE and the ones that already exist will remain, the inserts for those will be ingnored
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kyle - what's this operator "<>" - I want to learn more.

    r937 - I think that's what I'm going to do. Evidently Insert Ignore is pretty efficient in this use case. I'm going to do a little benchmarking but think that will work a lot better and take a huge step out of the equation.

    Thanks both.

  16. #16
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Or that, forgot about INGORE

  17. #17
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hifigrafix View Post
    Kyle - what's this operator "<>" - I want to learn more.
    Does not equal

  18. #18
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh ok... I use !=. Learn something new every day!

  19. #19
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    63 Post(s)
    Tagged
    2 Thread(s)
    That's PHP syntax. BTW, you'll still want to see if my query works, if so, you'll then convert that to an INSERT IGNORE statement.

  20. #20
    SitePoint Guru hifigrafix's Avatar
    Join Date
    Oct 2005
    Location
    Nashville, TN
    Posts
    628
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not to keep this thread going but... (for anyone else needing help with this)..

    I'm also going to look into ON DUPLICATE KEY which seems to benchmark faster when you aren't needing to validate data types.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    both <> and != are supported by mysql, but only <> is standard sql

    presumably the rows inserted in advance by users have additional data besides what you would produce when generating the 400 possible entries, so ON DUPLICATE KEY would not actually give you any additional fields to update, and IGNORE would make more sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •