Multiple Loops Producing Slow Script Execution

Databases
1

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


// 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\
")."', '".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

2

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

So what you want to do is make sure that each song is played once on each station each week?

4

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

You can think of it that way. There will be a record regardless of whether or not the station plays it.

Thanks

6

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

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

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

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 :slight_smile:

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

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

Just a heads up, record = row.

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

12 
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;

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;

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

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.


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

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

15

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

Or that, forgot about INGORE :rolleyes:

17

Does not equal

18

Oh ok… I use !=. Learn something new every day!

19

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

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.