erland;639301 Wrote:
> If you enable Logging of "plugin.custombrowse" on debug level, you will
> see all the SQL statements executed. That's a good start if you like to
> start optimize queries.
>
I isolated the slow query:
[11-07-06 20:54:01.5324]
Plugins::CustomBrowse::MenuHandler::SQLHandler::_execute (72)
Executing: select
customscan_track_attributes.attr,customscan_track_attributes.attr,substr(customscan_track_attributes.attr,1,1),customscan_track_attributes.valuetypefrom
customscan_track_attributes
join multilibrary_track on
customscan_track_attributes.track=multilibrary_track.track
and multilibrary_track.library=2
join customscan_track_attributes attr1 on attr1.module='mixedtag' and
customscan_track_attributes.track=attr1.track and
attr1.attr='COMPOSERSORT' and attr1.extravalue='Beethoven'
join
customscan_track_attributes currentattr on
currentattr.module='mixedtag' and
customscan_track_attributes.track=currentattr.track and
currentattr.attr='GENRE' and currentattr.extravalue='220'
where ((customscan_track_attributes.attr='GENRE' and
customscan_track_attributes.extravalue not in ('220')) or
(customscan_track_attributes.attr='COMPOSERSORT' and
customscan_track_attributes.extravalue not in ('Beethoven')) or
customscan_track_attributes.attr not in ('COMPOSERSORT','GENRE')) and
customscan_track_attributes.attr in
('GENRE','ARTIST','COMPOSERSORT','OPUS') and
customscan_track_attributes.module='mixedtag' group by
customscan_track_attributes.attr order by
customscan_track_attributes.attr
[11-07-06 20:55:03.2997]
Plugins::CustomBrowse::MenuHandler::SQLHandler::_execute (79) Executing
and collecting: select
The trouble is caused by the join with multilibrary_track.
In the end I moved the join to the end, just before the WHERE, and now
the speed is much better.
The query is prepared in CustomScan::Modules::MixedTag.pm, in sub
getMixedTagMenuItems, around line 468.
I have attached a patch, where the order of joins has been changed.
For the fun of it here are the speeds from the log file (from start of
this query to start of next one):
SQLite old module: 62 seconds
SQLite modified module: 140 ms
MySQL old module : 78 ms
MySQL modified module: 49 ms
Doesn't SQLite have an optimizer?
+-------------------------------------------------------------------+
|Filename: MixedTag.patch |
|Download: http://forums.slimdevices.com/attachment.php?attachmentid=12047|
+-------------------------------------------------------------------+
--
Co1
Co van Winsum
------------------------------------------------------------------------
Co1's Profile: http://forums.slimdevices.com/member.php?userid=34002
View this thread: http://forums.slimdevices.com/showthread.php?t=88627
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
