497433 - Adding index to test_runs speeds up getTests query

Status: RESOLVED FIXED

(Webtools Graveyard :: Graph Server, defect)

Description

[Chris AtLee [:catlee]]

Attachment: Adding index to test_runs table

Adding an index to (test_id, build_id, machine_id) speeds up the getTests query by about 0.7 seconds (about a 35% improvement).

I'm not exactly sure why it helps, maybe somebody with more SQL knowledge can chime in?

Comment 1

[Ryan Doherty (:rdoherty)]

I think Dave could do this for us since he's been our IT contact. Dave, let me know if I should assign it to someone else, thanks!

Comment 2

[Dave Miller [:justdave]]

It helps because you're running a query that pulls data using those three columns as criteria.  Any time you're running queries with the same columns as the criteria frequently, an index of those columns will help your data get located faster.  The tradeoff, of course, is the more indexes you add, the more disk space you use, and there's a slightly (but pretty much imperceptibly) longer delay on updates/inserts as the index has to be updated.

Where's the code this patch is against?  I need to know what table this is on to apply it to the databases.

Comment 3

[Ryan Doherty (:rdoherty)]

Chris, can you provide the SQL to run this against an already existing graphserver database?

Comment 4

[Chris AtLee [:catlee]]

I used this:

alter table test_runs add index test_id_3 (test_id, machine_id, build_id);

The patch is against sql/schema.sql in the graphs database.

Comment 5

[Dave Miller [:justdave]]

Applied to production:

mysql> alter table test_runs add index test_id_3 (test_id, machine_id, build_id);
Query OK, 937284 rows affected (25.48 sec)
Records: 937284  Duplicates: 0  Warnings: 0

Appears to already exist on stage.