288973 - Front page queries are sub-optimized

Status: RESOLVED FIXED

(addons.mozilla.org Graveyard :: Public Pages, defect)

Description

[Giorgio Maone [:ma1]]

Front page is obviously the most hit one of the entire site, hence its SQL
queries should be as optimized as possible.
index.php contains 3 queries wich extract unneeded fields and rows, followed by
PHP loops which emulate functionality easily achieved with sane SQL.
Looking at

http://lxr.mozilla.org/mozilla/source/webtools/update/index.php#141
http://lxr.mozilla.org/mozilla/source/webtools/update/index.php#182
and
http://lxr.mozilla.org/mozilla/source/webtools/update/index.php#224

needed changes are easily spotted:
1. don't extract TV.* fields, since they are not used
2. put a DISTINCT clause (depending on step 1)
3. add a LIMIT 5 clause
4. eliminate all the PHP code which emulate the above SQL from the data
consuming loops

I'll post a patch as soon as bug #287159 (which work on the same code) is fixed.

Comment 1

[Giorgio Maone [:ma1]]

Attachment: fixes queries and loops

Also coalesces redundant calls to uriparams() and ucwords()

Comment 2

[Michael Morgan [:morgamic]]

Comment on attachment 179709 [details] [diff] [review]
fixes queries and loops

Fix the two instances where a "$" was missing before uriparams -- then we can
call it good.

Comment 3

[Giorgio Maone [:ma1]]

Attachment: Fixed as morgamic said

Comment 4

[Michael Morgan [:morgamic]]

Comment on attachment 179830 [details] [diff] [review]
Fixed as morgamic said

Sometime later we can examine why uriparams is there at all.  Until we have
time for that, this is a good improvement.  Thanks for fixing those two lines.

Comment 5

[Michael Morgan [:morgamic]]

This has been committed.  Thanks, Mao.