I recently asked on /webdev about my relatively simple PHP/MySQL site crashing once it reached around 200 concurrent users, which doesn’t seem right because I have it hosted on a VPS server which should be powerful enough for that kind of traffic. People’s replies seem to indicate I may not have my MySQL table indexed and that could be a cause of poor performance.
“Indexing” has always been confusing to me, so curious if by looking at the image I attached anyone can tell anything (i.e. is it already indexed properly, is it not, are my mysql types possibly wrong, etc). The image shows that the table has two “indexes” listed but I don’t believe I ever created them so maybe they are automatic and not necessarily the right indexes?
If it helps, users enter their birthdate and from that info I get the year and the week (between 1 and 52) and then query the database to find the row that matches that year and that week. Seems super simple and straightforward but maybe I’m doing something horribly wrong haha.
Anyway, any analysis of the attached table info or other general guidance would be much appreciated (even if someone has a link to a good site or video that explains MySQL indexing like I’m 5, that would help a lot. When I google it and start reading I just get more lost haha).
There are just over 4,000 total rows. FWIW, the “poster” column is a full wikipedia image URL, like this for example: https://upload.wikimedia.org/wikipedia/commons/7/74/Ben_hur_1959_poster.jpg The rest are all just text fields. Maybe varchar is a bad choice for a URL column? Even if it is, I doubt it would be the reason for such poor site performance.
And now that I’ve learned a bit more since building this site and am looking at this now, I’m pretty sure the year and week columns should probably be “varchar” instead of “int”, but again, even if that’s right it probably doesn’t have significant bearing on performance.