Site Is So Slow!

[eluser]Hockeychap[/eluser]
Firstly morning gents.

@dmorin - you're quite right about the implementation of indicies on the join column fields. I look after a couple of medium size BI databases (~10sM rows and yes they are in MySQL) and one of our developers forgot about secondary indicies. Whilst the system worked fine for the 1st week, we had to define a heap load of indicies when we had slightly greater data volumes loaded.

@J_Slim - looking at the figures that you have provided, we should be able to bring your query time down to the fractions to small seconds per query. The hardware you have should be more than sufficient.

Looking at your explain plan, dmorin has quite rightly picked up on the fields that you need to index, although you only need to index the fields that you join to. From the plan there are three tables that are being scanned rather than index read. These are:

Meet_Events
Meet_Athletes
Roster

The fields that you need to create the indicies on are:

Meet_Events.Meet_ID
Meet_Athletes.Athlete_ID
Meet_Athletes.Meet_Event_ID
Roster.Coach_ID

(The left join to this table should actually just be

Code:

LEFT JOIN coaches AS Coach ON Roster.coach_id = 1000000 <the dynamic coach number>

rather than

Code:

LEFT JOIN coaches AS Coach ON Roster.coach_id = Coach.id AND Coach.id = 1000000 <the dynamic coach number>

as coach_id is actually a constant.

You will need to bring you database to a quiet state before you create these new indicies. Technically index creation locks the source table for writes whilst it creates the index. The best way to do this is (from experience of a couple of sites I run):

1. Post a note / email you registered users letting them know that you are planning a 10 minute outage and give them the times and dates.
2. Either put the site into maintenance mode or stop the apache server (this will stop new db connections being started).
3. Restart the mysql daemon. This will stop all currently running queries and let you database catch up.
4. Run you script to create the new indicies (should only take a couple minutes at the absolute worst case - again from the row counts that you have provided)
5. Restart the web server /move the website out of maintenance mode.

As a quick explanation of indicies and apols if I'm teaching you to suck eggs:

1. Primary index - used to identify exactly one row in the table
2. Unique index - also can be used to identify one row, but from a non-primary key view
3. Index - simply a list of row ids for the fields in your index.

The difference between 1 and 2 is very slim and usually occurs where you have a table something like:

Field 1 - Autogenerated row number
Field 2 - Athletes Social Security Number
Field 3 - Athletes home town

You would have a primary index on field 1 (it's known as a synthetic key)
You could have a unique index on field2 (no to people should have the same social securiy number)
You could have a normal index on field 3 if you wanted to quickly find everyone who came from London.

From a mysql point of view and given your table sizes, the rule of thumb rule is that if you are running a query that will retrieve less than 20-25% of your table, then a secondary index would be of benefit. You will incur a very, very ,very small penalty on writes and gain massively on your select statements.

Hope this all helps.