2014-08-14, 00:09
(2014-08-13, 23:49)d0wnl0rd Wrote: Just to give you a benchmark on a really low-powered NAS: mysql running on Synology DS210j provides me with 3,0x seconds running that query with a N-parameter (giving 4 rows), and 3.1x secondy for a full query (giving 231 rows). 81 tvshows, 458 seasons, 3.241 episodes.
Another factor may be the amount of RAM available to the MySQL server instance. Relational databases love memory more than horsepower, but a database starved of RAM may be forced to hit the scratch area much harder. Much of this is "tuneable" in my.cnf assuming you have the RAM to spare. My configuration is custom (FreeNAS with MySQL running in a jail) and RAM isn't really an issue which may mean it has to use the scratch area much less (if at all). For reference, here's the my.cnf I'm using (dual-core processor, 8GB RAM).
I would suggest continuing this discussion in the goRt's thread as it's not Pi specific and may get more attention from non-Pi users that may also be impacted by this (if not now, then eventually). If there are any changes/optimisations forthcoming on the schema side of things I'll be sure to include them in a future build.