andyg;512963 Wrote:
> On Feb 2, 2010, at 2:33 PM, dsdreamer wrote:
> > Am I right? Anyone else see this behavior?
>
> I'd be surprised, since this query is basically instant in SQLite. I
> will get back to some MySQL testing in the next day or so, and see if I
> can spot any problems.
This is definitely a problem, joining two "text" columns in MySQL is a
big no no, at least in the MySQL version we use. The problem is that
MySQL isn't able to use any indexing when doing this. I have the exact
same problem in TrackStat plugin when I like to join tracks and
tracks_persistent, it basically doesn't work in MySQL. Joining a varchar
with a "text" column is fine, but joining two "text" columns doesn't
work.
Here is the explain execution to prove it:
Code:
--------------------
explain SELECT scanned_files.url FROM scanned_files JOIN tracks ON (
scanned_files.url = tracks.url AND ( scanned_files.timestamp !=
tracks.timestamp OR scanned_files.filesize != tracks.filesize ) AND
tracks.content_type != 'dir' ) WHERE scanned_files.url LIKE 'file:///mnt%';
+----+-------------+---------------+------+----------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+---------------+------+----------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | scanned_files | ALL | scannedUrlIndex | NULL |
NULL | NULL | 3429 | Using where |
| 1 | SIMPLE | tracks | ALL | ctSortIndex,urlIndex | NULL |
NULL | NULL | 2955 | Using where |
+----+-------------+---------------+------+----------------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
--------------------
In my 3200 track library this query takes about 7.5 seconds.
If anyone finds a way to make it use indexes in this case, I'm very
interesting as it would solve some serious performance issues with some
of the TrackStat queries.
--
erland
Erland Isaksson
'My homepage' (http://erland.isaksson.info) (Install my plugins through
Extension Downloader)
(Developer of 'TrackStat, SQLPlayList, DynamicPlayList, Custom Browse,
Custom Scan, Custom Skip, Multi Library, Title Switcher and Database
Query plugins'
(http://wiki.erland.isaksson.info/index.php/Category:SlimServer))
------------------------------------------------------------------------
erland's Profile: http://forums.slimdevices.com/member.php?userid=3124
View this thread: http://forums.slimdevices.com/showthread.php?t=74700
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
