468461 - Add an index to type column for tiki_comments

Status: RESOLVED INCOMPLETE

(support.mozilla.org :: Forum, task)

Description

[[:Cww]]

Right now, searching for solved or "proposed solved" problems is killing my computer.  An index on the `type` column should help.

Although I could also be doing it wrong.

Comment 1

[[:Cww]]

If this is any incentive, this will really really help performance of queries done with the forum filtered per bug 444331

And double incentive: here's the one-line SQL QUERY that will fix this bug

ALTER TABLE mozdump.tiki_comments ADD INDEX type(`type`);

(Of course, feel free to add more stuff if you think it's necessary)

Comment 2

[[:Cww]]

CCing Laura.  This shouldn't require a push even so can it be done off schedule?

Comment 3

[David Tenser [:djst]]

(In reply to comment #0)
> Right now, searching for solved or "proposed solved" problems is killing my
> computer.  An index on the `type` column should help.
> 
> Although I could also be doing it wrong.

What do your queries look like?

Comment 4

[[:Cww]]

When doing a full count of how many threads are each type:

SELECT type, COUNT(1) FROM `tiki_comments` tc WHERE tc.object = 1 and tc.objectType = 'forum' and tc.parentId = 0 and FROM_UNIXTIME(commentDate) BETWEEN CAST('2008-12-02' AS Date) AND CAST('2008-12-09' AS Date) GROUP BY type;

Or even when I'm just pulling up a list of threads:

SELECT DISTINCT IF(parentId = 0, threadId, parentId) AS 'threads'
FROM tiki_comments WHERE type = 'r' AND `object`=1 AND `objectType` = 'forum' ORDER BY `commentDate` DESC LIMIT 20

(This pulls up the thread numbers of the last 20 commented-on threads which are marked request for more info... I do a secondary query on these to get the actual contents of the threads).

I've found it actually works faster to just build an index using ALTER TABLE ADD INDEX when I import the database since the time savings is made up for after just a couple queries.  This takes 15 minutes (during which computer is unusable) though and is clobbered every time I import again (at least once a week, sometimes twice).  It'd be nice just to have it there already.

Comment 5

[[:Cww]]

Attachment: query from comment 1

Made a patch so I can get review.

Comment 6

[Paul Craciunoiu [:paulc]]

Cheng, perhaps we should add any not-currently-existing indexes from bug 498105 as well.

Comment 7

[Laura Thomson :laura]

I would suggest not conflating the patches.  If you need those indexes make this bug dependent on that one.

Comment 8

[[:Cww]]

From bug 498105 comment 7: we don't actually need new indexes in addition the this one for bug 498105.  This patch should stand as is.

Comment 9

[[:Cww]]

This patch is really low risk, can we just take it?

Comment 10

[[:Cww]]

(btw, this is just SQL, so should I just file a bug to have it run on staging and resolve this fixed?)

Comment 11

[[:Cww]]

Attachment: db-name-neutral query

took out DB name since that's not the same on all installs

Comment 12

[Laura Thomson :laura]

Could we get some benchmarks as well?

Comment 13

[[:Cww]]

I couldn't tell on sumotools so I guess we don't need to do this.  (It seems like a good idea though.