116 reputation
2
bio website
location
age
visits member for 2 years, 9 months
seen Apr 3 at 21:54

Apr
9
awarded  Popular Question
Feb
3
comment Optimize Query with Derived Table
Not sure what's going on here....I upgraded my local MYSQL Server version to 5.5.19 and I still get the same error when I run the above query....not sure how you got it running? I have read that MYSQL does not support LIMIT in subqueries at all....dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html
Feb
3
comment Optimize Query with Derived Table
Unfortunately, I am not in a position to upgrade MySQL just yet...
Feb
2
comment Optimize Query with Derived Table
Interesting....however when I run, I get this - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' and I am on MySQL 5.5.10
Feb
2
comment Optimize Query with Derived Table
In other words, are there any better alternatives for derived tables?
Feb
2
comment Optimize Query with Derived Table
Those tables and extra fields are using INDEXES correctly, however, my question was could this subquery be written in any better way than above because looking at the EXPLAIN, this is a full table scan even though I have some indexes on tblstatistics defined.
Feb
2
comment Optimize Query with Derived Table
I did not run the EXPLAIN on production DB which is why you see 2643 and not 100,000 + and there are a lot other WHERE conditions which I excluded from my question.
Feb
2
awarded  Student
Feb
2
asked Optimize Query with Derived Table