On Sat, Feb 21, 2004 at 02:39:00PM +0000, Andre Engels wrote:
Trying to keep [[en:Wikipedia:Protected pages]]
up-to-date, I want to run
the SQL query
SELECT cur_namespace,cur_title FROM cur WHERE cur_restrictions="sysop"
However, this gets hit by the 30 seconds limit, even with a LIMIT as low as 30.
Which I find strange as it seems to be a rather simple search, not more or
less complicated than, say:
SELECT cur_namespace,cur_title FROM cur WHERE cur_namespace=9
for which the complete results (ca. 50 items) appear 1 second or so.
Is cur_restrictions for some reason so very hard to reach? And if so, why is
it, and what can be done about it?
From indexes.sql:
ALTER TABLE cur ADD INDEX
cur_namespace (cur_namespace).
But no index on cur_restrictions. So finding all pages with
cur_restrictions='sysop'
requires checking whole database. And if there are 300 protected pages,
LIMIT 30 means 10% of the database needs to be checked, what's still extremely heavy.
We really need to change database structure a bit.
I'd suggest:
CREATE TABLE cur2 (cur_id, cur_text) ;
ALTER TABLE cur DROP COLUMN cur_text;
ALTER TABLE cur ADD COLUMN cur_text_length;
increase mysql buffer sizes if they aren't big enough (standard sure aren't,
I'm not sure what are current sizes)
With this, the queries would have to load only much smaller cur, not huge cur2.
(of course I'm not sure whether it will be any better, but it seems worth testing)