Site Is So Slow! |
[eluser]dmorin[/eluser]
Cool, that helps a lot. So a good exercise when starting with explain is to multiply all of the numbers in the "rows" column together. This is the max number of rows that mysql has to look through the get the result. Because you have a where it won't be quite this high but it's still a good starting point. In your case, it's 803,387,392,390. This starts to explain why your site is a little on the slower side. So to start, if you're comfortable changing the sql schema, make sure that you have indexes on each of these fields: Quote:meet_events.id I'll be the first to admit that I'm not the best at identifying which fields need indexes, and someone might come along and say having one on one of these fields won't help, but because these are all involved in your join or where statements, it's a pretty safe bet that all of these should have indexes. Primary key fields should obviously have the Primary key type. If it should be unique use that kind, otherwise just use a plain index. If you have a development server to do it on first, that's good. After you've made the changes, rerun the explain query and post back here. Hope this is helping. Let us know if you've had enough feedback and want us/me to stop or if you're ok to keep going :-) Edit: for more info, check out http://hackmysql.com/case4 - Decent article on joins
[eluser]Jay Logan[/eluser]
This has been a hell of an experience and I really, really appreciate all the help. I definitely do NOT want you to stop. At least not until the issue is resolved. The only thing that sucks is all the e-mails I get from my paying users so sorry if I seem desperate on here. But I guess these steps are necessary in order to troubleshoot the problem. I don't know exactly which fields should be indexes either. I do see that most of those fields are set as primary though. So should I additionally make them an index? Or should I replace the primary with index? Or does it not even matter.
[eluser]dmorin[/eluser]
No, if they are primary indexes, they should certainly stay that way. You just want to make sure that the fields listed previously that don't currently have an index get one.
[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.
[eluser]Krzemo[/eluser]
Your explain plan shows that optimizer is doing probably full scan (has no index to use) on 3 tables: - Meet_Event - Meet_Athlete - Roster For sure there are primary keys on those. I assume that it is caused by query using columns: - meet_events.event_id - meet_athletes.meet_event_id - meet_athletes.athlete_id - roster.athlete_id - roster.coach_id So, bascially creating indexes on those columns is a start. Check query explain plan after it to make sure there is improvement.
[eluser]Jay Logan[/eluser]
Wow. So I checked my those tables/fields you both mentioned and for every field that was not already marked as PRIMARY, I made into an INDEX. I had already put my site into "Maintenance Mode" using .htaccess and as soon as I turned the site back LIVE, I noticed an increase in speed. One page in particular (the page I initially referred you all to) loads almost instantly. I'm going to see how things play out once users start taxing the DB later today but I think you guys nailed it. I can't believe a few missing INDEXES threatened my web site's business. You guys don't even know what I've had to go through so I really, really appreciate your time and help. All of you. Thanks again. Maybe I should check on those numbers again.
[eluser]Hockeychap[/eluser]
Have to say that's excellent news. Congrats and good luck (oh and we'll be here if anything else crops up ). Oh forgot to say - top site BTW. |
Welcome Guest, Not a member yet? Register Sign In |