[SOLVED] NOT EXISTS optimization

[Matrixkid]

Hi there,

I am running a query right now but the database is getting too big and the query is timing out half of the times - Query took 84.7006 sec - and I have to run this query on 8 tables  :-\

 

The query is:

weeknum is dynamic and relates to the current week of the year, ex: 3, yearnum is the current year.

SELECT Count( song ) AS CountSong, artist , song FROM music  
WHERE weeknum = ".$weekNum." AND yearnum =".$yearNum." 
AND song NOT IN (SELECT song FROM music WHERE yearnum = ".$yearNum." AND weeknum < ".$weekNum." ) 
GROUP BY song ORDER BY `CountSong` DESC

 

Basically what this does is grab the song and artist that has not appeared in the database prior to the current week. So essentially, Im getting a list of this weeks new songs.

 

I tried to use a left join, but I cant seem to figure it out.

I get #1052 - Column 'song' in field list is ambiguous

I tried:

SELECT song FROM music m
          LEFT JOIN music as x ON m.song = x.song AND x.weeknum = $weekNum-1
          WHERE m.weeknum = $weekNum    
          AND x.song IS NULL

 

Im just looking for some help to drop that query time from 86 seconds. lol.

thanks!

[Mchl]

Could you explain how is the data organised in this table?

It seems to me, that it needs normalising...

[Matrixkid]

Yep no problem.

 

The table looks like this:

Song         |  Weeknum | Yearnum
-----------------------------------
Song  1     |  3        | 2009
Song  2     |  3        | 2009
Song  3     |  3        | 2009
Song  4     |  3        | 2009
Song  1     |  2        | 2009
Song  3     |  2        | 2009
Song  1     |  1        | 2009
Song  5     |  1        | 2009

 

I have left out artist as its not as important as song.

Basically I have the song, a week number value, and a year number value.

 

So using this example, Song 2 and Song 4 are new songs for the current week (we are in week 3 of the current year). This is the result that I want.

 

Cheers!

[Mchl]

What is the meaning of Yearnum and Weeknum?

In other words: why one song may be present more than once in this table?

[Matrixkid]

What is the meaning of Yearnum and Weeknum?

In other words: why one song may be present more than once in this table?

 

All the songs played are thrown into the table. I couldnt think of a better way for comparing data and analyzing the songs for statistics.

 

When a song is played, it is thrown into the table, with a weeknum value(the weeknum is date("W") in php, so 1-52) and yearnum is the current year the song was played in (date("Y") in php)

 

This way I can count songs for certain weeks(for overplaying, underplaying, ect) and also for the current year.

 

I should add that there is also a unique time stamp for each song (it looks this this: January17.0451PM.2009)

[Mchl]

I see.

 

So if I understand correctly, you would like a query, that selects the songs (and how many times were thay played) that were never played prior to current week.

 

I would certainly recommend splitting this into two tables.

 

One would contain only information about songs

(ID, title, artist, genre etc...)

 

Second one would contain information about when the song was played

 

In the first table you could also store date, when the song was played for the first time.

 

Let's see

 

TABLE songs
songID, Song, ..., FirstPlayedOn

TABLE playHistory
songID, Date

 

Now to get the information you want, you would do

 

SELECT 
  COUNT(h.*) AS CountSong,
  s.Song AS Song
FROM 
  playHistory AS h
INNER JOIN
  songs AS s USING (songID)
WHERE
  WEEK(s.FirstPlayedOn)  = WEEK(NOW()) 
  AND YEAR(s.FirstPlayedOn) = YEAR(NOW())
  AND WEEK(h.Date) = WEEK(NOW())
  AND YEAR(h.Date) = YEAR(NOW())
GROUP BY
  title
ORDER BY
  CountSong

 

I did not test any of that, but I think it should work better.

 

Also: you might try to speed up your current query by checking if you have proper indexes defined.

 

Use

EXPLAIN yourQueryHere

 

to display information on what indexes (if any) are used.

 

[Matrixkid]

Thanks for your considerations.

Ill have to look into moving it to two tables, its just that I have over 50,000 records in this table for playing info.

But its probably best to get it out of the way and start fresh and efficiently.

 

Is there any other method possible with using only a single table?

My left join statement should speed things up a lot, but I cant seem to get it to work.

 

Also, here is the EXPLAIN query results

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	       Extra
1 	PRIMARY 	       music 	ALL 	NULL 	       NULL 	NULL 	   NULL 	57332 	Using where; Using temporary; Using filesort
2 	DEPENDENTSUBQUERY music 	ALL 	NULL 	       NULL 	NULL 	   NULL 	57332 	Using where

[Mchl]

As you can see your query does not use any indexes now (key - NULL) as there are no indexes available (possible_keys - NULL)

Try adding index covering both yearnum and weeknum columns

 

ALTER TABLE `music` ADD INDEX `WeekYear`(`weeknum`, `yearnum`);

 

and change your query so that it checks for week first and year later

WHERE weeknum < $weekNum AND yearnum = $yearNum

[Matrixkid]

I did as you said but it didnt help in any way.

Still takes around 80+ seconds for the query to run, and if I run more than 1 of these queries:

MySQL said: Documentation

#1053 - Server shutdown in progress

 

:S

Might have to start moving to two tables.

Cheers!

 

[Mchl]

What does EXPLAIN say now?

[Matrixkid]

Exact same thing - still NULL

 

id  	 select_type  	 table  	 type  	 possible_keys  	 key  	 key_len  	 ref  	 rows  	 Extra
1 	PRIMARY 	top40 	ALL 	NULL 	NULL 	NULL 	NULL 	56513 	Using where; Using temporary; Using filesort
2 	DEPENDENT SUBQUERY 	top40 	ALL 	NULL 	NULL 	NULL 	NULL 	56513 	Using where

[Mchl]

I see you changed the table name, so here comes silly question: did you create index for this table?

The index should at least show up in possible_keys.

[Matrixkid]

I see you changed the table name, so here comes silly question: did you create index for this table?

The index should at least show up in possible_keys.

 

lol you're right. too many tabs open.

 

heres the right table info:

id  	 select_type  	 table  	 type  	 possible_keys  	 key  	 key_len  	 ref  	 rows  	 Extra
1 	PRIMARY 	           music 	ref 	     WeekYear 	   WeekYear 	8 	   const,const 1071 	Using where; Using temporary; Using filesort
2 	DEPENDENT SUBQUERY 	music 	range 	   WeekYear 	   WeekYear 	4 	        NULL 	3921 	Using where

 

 

and hey hey, look what we got here,

89 total, Query took 7.3527 sec)

 

Wow. that worked nicely. A lot better than 80. lol

 

Thanks for the help Mchl! I really appreciate it.

 

I was just wondering if you could explain the index and how it works, and do I have to run the alter query every so often or does it update itself?

[Mchl]

Will you mind if I refer you to the manual for this?

http://dev.mysql.com/doc/refman/5.1/en/indexes.html

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

 

To answer the second part: no, you don't have to run ALTER query every now and then. Once is enough.

[Matrixkid]

Will you mind if I refer you to the manual for this?

http://dev.mysql.com/doc/refman/5.1/en/indexes.html

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

 

To answer the second part: no, you don't have to run ALTER query every now and then. Once is enough.

 

Nope not a problem.

 

Thanks again!

[Mchl]

You're welcome

 

Now that you have the most urgent problem solved, think a little about redesigning your database. I (or other Freaks here) can help of course.