713144 - The SQL query to remove older searches from the profile_search table should be more robust

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, defect)

Description

[Frédéric Buclin]

Attachment: patch, v1

For some unknown reasons, it may happen that the profile_search table stores more than SAVE_NUM_SEARCHES (default 10) recent searches per user. For instance, bmo stores 5532 buglists for me, and 207422 for dsicore! This makes viewing a bug slower, because Bugzilla scans them all to see which buglist to use for the Prev/Next navigation links. We should improve the SQL query to really delete older queries.

Comment 1

[Dave Miller [:justdave]]

For comparison, here's what I actually ran on the database server to clean out the overages:

# mysql --skip-column-names -B -e 'select concat("DELETE FROM profile_search WHERE user_id=", search_counts.user_id, " AND id <= ", (select id from profile_search where user_id=search_counts.user_id order by id desc limit 10,1), ";") from (select user_id, count(id) as num from profile_search group by user_id) as search_counts where num > 10;' bugs | mysql bugs

Comment 2

[:glob ✱]

ouch.

Comment 3

[Frédéric Buclin]

Comment on attachment 583958 [details] [diff] [review]
patch, v1

Asking glob too as I saw that justdave was away till Dec 27.

Comment 4

[David Lawrence [:dkl]]

Comment on attachment 583958 [details] [diff] [review]
patch, v1

Review of attachment 583958 [details] [diff] [review]:
-----------------------------------------------------------------

Looks good and works as expected. r=dkl

Comment 5

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/Search/Recent.pm
Committed revision 8051.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.2/
modified Bugzilla/Search/Recent.pm
Committed revision 7987.