Don't just skip past indexes!

It’s been a little while since my last blog post. With various new projects that I am working on I have been really busy (and looks like i’m set to stay this busy for a long time yet!)

I have been working on a project over the last few weeks, codename: “Secret Squirrel”. Which includes a large volume of data being regularly viewed, updated & reports generated based on the users requests. Unlike projects I have worked on before (which have an attitude of: We’ll just load that into a table overnight/hourly etc, and select * from reporting_table_a)); this project has focussed on this information all being real-time. Whilst looking at the capacity for MySQL, compared with other DB’s; along with the fact that I will be managing this project ongoing, I have decided to stick with MySQL. Switching the DB is a pretty big risk, especially if theres a problem I need to fix - I need to be working with what I understand.

Yes I know this isn’t a good query, but it will explain the issues I found.

I put an index on:

Employee:

employee_name

employee_identifier

employee_id

Employee Details:

employee_phonenumber

employee_id

And thought this was enough, but this ran awfully when a lot of data is present. I overlooked the need for the following index on roles:

role_id

employee_id

The tip is, don’t just presume the issue is that you have such a huge amount of data, check the indexes first! I wen’t through changing character sets, switching from InnoDB to MyISAM and back again, changing the my.cnf file to allocate more resources.

About me

Matt Clements is a Web Developer based in the vibrant town of Milton Keynes, specialising in PHP, MySQL, Sass, Javascript and much more.

Matt is an adaptive developer who will adjust and adapt to any development project, and will help you reach your final goal.