Welcome Guest, Not a member yet? Register   Sign In
How to structure the database - MYSQL performance question

I am re-writing a legacy application in CI Bonfire. The app will be using the old database which I do have scope for re-structuring if required.

Most apps I've worked on I've always used heavily normalised databases but I know that this can have implications on performance for larger applications, which this is so I'm less inclined to normalise and instead leave the structure flatter.

I also think that heavy normalisation was more useful when business logic was encapsulated in the database structure, whereas these days we would do that in the application code.

The app hosts submissions of aircraft pictures (getting on for 1 million entries). When a new aircraft is submitted the manufacturer, model and type need to be looked up. These details are currently held in a single table, see screenshot for a sample of the data:

[Image: Image%202015-09-30%20at%2012.54.48%20PM.png]

In the legacy app this is pulled into dropdowns using SQL similar to the following:

SELECT * FROM aircraft_table GROUP BY manufacturer ORDER BY manufacturer ASC

Now the easiest thing to do would be to keep with the same system but I am concerned by the performance hit of the GROUP BY.

The other option is to normalise into Manufacturer, Manufacturer_Generic_Type, Generic_Type, Generic_Type_Type, Type tables which will obviously involve a lot of joins and come with its own performance overhead.

I had thought about using ENUMs but the application users need to be able to add new aircraft types so this solution doesn't work for that case.

All of the tables are InnoDB,they were MyISAM but I'm moving over to InnoDB primarily for performance improvements, mainly in avoiding table locking.

I'm no MySQL expert, particularly not where performance is concerned, so I'm looking for advice on what would be the best way to structure this table and others like it for optimum performance. Could some of these issues be overcome by careful use of indexes (another area in which I am a complete novice)? Any other advice?


Messages In This Thread
How to structure the database - MYSQL performance question - by jhob - 09-30-2015, 05:07 AM

Theme © iAndrew 2016 - Forum software by © MyBB