Installed and seems to work. Tho weighting seems to be quite a bit off (haven't looked at that portion of code yet).
Quick glance at DB shows no reason why it should be slow, if queries match. So those who are having speed issues probably have bad MySQL config just. MySQL scales really efficiently and i don't see immediate reasons why this would be slow, but have to wait for data set to increase.
If anyone got say 8G+ dataset they don't mind sharing, i would be willing to look into it. Or any size with which someone is having some serious perf issues.
Code should be PLENTY more commented btw
Quick glance at DB shows no reason why it should be slow, if queries match. So those who are having speed issues probably have bad MySQL config just. MySQL scales really efficiently and i don't see immediate reasons why this would be slow, but have to wait for data set to increase.
If anyone got say 8G+ dataset they don't mind sharing, i would be willing to look into it. Or any size with which someone is having some serious perf issues.
Code should be PLENTY more commented btw
Check out the join that creates the status display.
LOL! Yeah that would cause some serious issues (first query in admin/index.php) 3rd query is a monstrosity.
Well there is the problem, using dynamic (on the fly created) tables etc.
These queries is almost like SELECT *, wonder if they ever hit any indexes ...
In any case need bigger data set before i can optimize them properly.
But on first query FROM ( XXXX XXXX ) sw should be change to just choose the damn table, moving the LIMIT 10 at the whole query.
Inner joins -> just choose from multiple tables, use same p.id = sw.pool_id
So something like
SELECT w.name AS worker, p.name AS pool, p.pool_id AS poolId, p.id AS poolId, sw.worker_id AS workerId [AND SO ON FOR ALL FIELDS REQUIRED]
FROM submitted_work sw, pool p, woker
WHERE pool.id = sw.pool_id AND worker.id=sw.workerid
ORDER BY
LIMIT
INNER JOINS are like SELECT *, if i recall right
Bottomline is that carefully crafted queries can do a full text match scored on a 100G dataset with multiple text matches, joining multiple tables (not using JOIN clause tho, but still called joining), on a Quad Core Xeon with 16G ram (pre-i7 xeon) in well under 100ms. (Target was 15searches/sec, achieved peak was above that, and bottleneck was actually the PHP syntax parsing for real world scenario which transformed our simplified custom query language into a mysql query, for easier use for the end users)
