heres a screenshot of the output
http://i.imgur.com/vXVfhEc.jpg
And here is the warnings output
mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'tvshowview.idShow' of SELECT #3 was resolved in SELECT #2
|
| Note | 1003 | select `tvshowview`.`idShow` AS `idShow`,`tvshowview`.`c00` AS `c00`,`tvshowview`.`c01` AS `c01`,`tvshowview`.`c02` AS `c02`,`tvshowview`.`c03` AS `c03`,`tvshowview`.`c04` AS `c04`,`tvshowview`.`c05` AS `c05`,`
tvshowview`.`c06` AS `c06`,`tvshowview`.`c07` AS `c07`,`tvshowview`.`c08` AS `c08`,`tvshowview`.`c09` AS `c09`,`tvshowview`.`c10` AS `c10`,`tvshowview`.`c11` AS `c11`,`tvshowview`.`c12` AS `c12`,`tvshowview`.`c13` AS `c13`,`tvs
howview`.`c14` AS `c14`,`tvshowview`.`c15` AS `c15`,`tvshowview`.`c16` AS `c16`,`tvshowview`.`c17` AS `c17`,`tvshowview`.`c18` AS `c18`,`tvshowview`.`c19` AS `c19`,`tvshowview`.`c20` AS `c20`,`tvshowview`.`c21` AS `c21`,`tvshow
view`.`c22` AS `c22`,`tvshowview`.`c23` AS `c23`,`tvshowview`.`strPath` AS `strPath`,`tvshowview`.`dateAdded` AS `dateAdded`,`tvshowview`.`lastPlayed` AS `lastPlayed`,`tvshowview`.`totalCount` AS `totalCount`,`tvshowview`.`watc
hedcount` AS `watchedcount`,`tvshowview`.`totalSeasons` AS `totalSeasons` from `myvideos75`.`tvshowview` where <in_optimizer>(`tvshowview`.`idShow`,<exists>(select 1 from `myvideos75`.`tvshowview` where ((((`tvshowview`.`watche
dcount` > 0) and (`tvshowview`.`watchedcount` < `tvshowview`.`totalCount`)) or ((`tvshowview`.`watchedcount` = 0) and <in_optimizer>(`tvshowview`.`idShow`,<exists>(select 1 from `myvideos75`.`episode` join `myvideos75`.`files`
join `myvideos75`.`tvshow` left join `myvideos75`.`seasons` on(((`myvideos75`.`seasons`.`idShow` = `myvideos75`.`tvshow`.`idShow`) and (`myvideos75`.`seasons`.`season` = `myvideos75`.`episode`.`c12`))) join `myvideos75`.`path`
join `myvideos75`.`bookmark` where ((`myvideos75`.`episode`.`idShow` = `myvideos75`.`tvshow`.`idShow`) and (`myvideos75`.`path`.`idPath` = `myvideos75`.`files`.`idPath`) and (`myvideos75`.`bookmark`.`type` = 1) and (`myvideos75
`.`bookmark`.`idFile` = `myvideos75`.`episode`.`idFile`) and (`myvideos75`.`files`.`idFile` = `myvideos75`.`episode`.`idFile`) and (`myvideos75`.`episode`.`idShow` = `tvshowview`.`idShow`) and (`myvideos75`.`bookmark`.`timeInSe
conds` > 0) and (<cache>(`tvshowview`.`idShow`) = `myvideos75`.`tvshow`.`idShow`)))))) and (<cache>(`tvshowview`.`idShow`) = `tvshowview`.`idShow`)))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------+
edit:dropped all tables and scanned in most of my tv shows in again (about 14k). Its taking 43 seconds roughly to run the query. Is is that just that this query is slow at handling large databases? or is there something that can be done to speed it up?