wow, there is many kinds of bad things going on code QA wise.
This code is what i call confused newbie abstraction.
Using functions to output HTML in a view (already outputting HTML)? Yup!
Using abstration function to output a simple form image button? Yup!
Doing insane joined and dynamic tables query for a few fields of simple data? Yup!
Anyways, 2 first queries optimized, one less table to lookup, indices being actually hit. Far from being completely optimized (still a filesort happening!), but should proof to be an order of magnitude faster, DESPITE hitting more rows. I have NO way to test however, nor profile correctly due to lack of dataset size, so measurements would be less than error of margin.
Replace admin/index.php 2 first queries with those found at: http://pastebin.com/hwncLV1w
NOTE: I have not done proper testing, results seem to be correct tho
EDIT: Looking into 3rd query now, it's worse than expected. Will rework complete query and accompanying view portion. It actually checks all rows multiple times, assuming MySQL realizes how to optimize. It makes 5 dynamic (on-the-fly created) tables, hits *ALL* rows on submitted work, creates temp tables (lack of suitable index), 3 times filesort, 13 tables total, and if i interpret correctly total rows to go through is in the range of hundreds of thousands rows or more with my 2526 shares submitted test data! :O
This code is what i call confused newbie abstraction.
Using functions to output HTML in a view (already outputting HTML)? Yup!
Using abstration function to output a simple form image button? Yup!
Doing insane joined and dynamic tables query for a few fields of simple data? Yup!
Anyways, 2 first queries optimized, one less table to lookup, indices being actually hit. Far from being completely optimized (still a filesort happening!), but should proof to be an order of magnitude faster, DESPITE hitting more rows. I have NO way to test however, nor profile correctly due to lack of dataset size, so measurements would be less than error of margin.
Replace admin/index.php 2 first queries with those found at: http://pastebin.com/hwncLV1w
NOTE: I have not done proper testing, results seem to be correct tho
EDIT: Looking into 3rd query now, it's worse than expected. Will rework complete query and accompanying view portion. It actually checks all rows multiple times, assuming MySQL realizes how to optimize. It makes 5 dynamic (on-the-fly created) tables, hits *ALL* rows on submitted work, creates temp tables (lack of suitable index), 3 times filesort, 13 tables total, and if i interpret correctly total rows to go through is in the range of hundreds of thousands rows or more with my 2526 shares submitted test data! :O