3 ms·

Yes, mySQL. I'm sure I've made loads of mistakes in structuring things. I don't know where to start checking my indexes for mistakes though. Any resources yo

Yes, mySQL. I'm sure I've made loads of mistakes in structuring things. I don't know where to start checking my indexes for mistakes though. Any resources you can point me at that are somewhat newbie friendly?

kls 15y ago
First thing to do is look at you where and order/group by clauses almost all of the items listed after them should have an index on them. Many should have a common index on the columns together, if they are used together a lot to filter information.
- kellyreid 15y ago
  hmm. that makes a lot of sense. can you suggest some reading that can help me understand which types to use? I'm almost entirely self-taught on SQL so the result is "functional" code that's just not well-designed or optimal
  - deleted 15y ago
    [deleted]
  - kls 15y ago
    I don't do much mySQL but indexing should be similar to other platforms, I found this slide deck that covers the basics for mySQL http://www.slideshare.net/osscube/indexing-the-mysql-index-key-to-performance-tuning http://www.slideshare.net/osscube/indexing-the-mysql-index-k... . It will get you started down the path and from there you can use Google to expand on the concepts. At it's basis though indexing is pretty easy, it makes a map of items that are used to filter a query, think of them as shortcuts to the rows you are looking for. If there is no index their is no map and therefore the DB has to table spool through each record to find it. Composite indexes are where you have two or more columns that need to be search on, so say you have a WHERE gender = 'M' and age = 18 an composite index builds a map for both columns together. So you may have multipal indexes on your table based on your requirements, you may have a single index on gender to improve a query that gets all males in the system, while you may also have an index on age and gender to aid in querying all males that are 18. Most DB have a index tuning engine, that when turned on will analyze queries that come in and make recommendation as to how to tune the DB. I have not used mySQL in a long time so I don't know if they do, if so it would be worth, turning it on, running through the entirety of your app and collecting the data. If you need further recommendations, just hit me up on Skype, and I can give you a hand in real time, it will be much easier than using HN to help you out. My info is in my profile.

kls 15y ago

First thing to do is look at you where and order/group by clauses almost all of the items listed after them should have an index on them. Many should have a common index on the columns together, if they are used together a lot to filter information.

kellyreid 15y ago
hmm. that makes a lot of sense. can you suggest some reading that can help me understand which types to use? I'm almost entirely self-taught on SQL so the result is "functional" code that's just not well-designed or optimal
- deleted 15y ago
  [deleted]
- kls 15y ago
  I don't do much mySQL but indexing should be similar to other platforms, I found this slide deck that covers the basics for mySQL http://www.slideshare.net/osscube/indexing-the-mysql-index-key-to-performance-tuning http://www.slideshare.net/osscube/indexing-the-mysql-index-k... . It will get you started down the path and from there you can use Google to expand on the concepts. At it's basis though indexing is pretty easy, it makes a map of items that are used to filter a query, think of them as shortcuts to the rows you are looking for. If there is no index their is no map and therefore the DB has to table spool through each record to find it. Composite indexes are where you have two or more columns that need to be search on, so say you have a WHERE gender = 'M' and age = 18 an composite index builds a map for both columns together. So you may have multipal indexes on your table based on your requirements, you may have a single index on gender to improve a query that gets all males in the system, while you may also have an index on age and gender to aid in querying all males that are 18. Most DB have a index tuning engine, that when turned on will analyze queries that come in and make recommendation as to how to tune the DB. I have not used mySQL in a long time so I don't know if they do, if so it would be worth, turning it on, running through the entirety of your app and collecting the data. If you need further recommendations, just hit me up on Skype, and I can give you a hand in real time, it will be much easier than using HN to help you out. My info is in my profile.