Thread: MySQL optimization concerns
10-11-12, 04:41 #1Registered User
- Join Date
- Jul 2011
Unanswered: MySQL optimization concerns
If I have a table with hundreds of thousands of records:
- Does it matter to shorten database field names. For example from 'key_category_item_id' to 'cid'
- Should I use 'tinyint' instead of 'varchar' where I can. For example 0 for 'header' and 1 for 'footer'.
- How does partitioning a table helps and what are disadvantages of it.
- Other than disk space issues, why shouldn't I create more indexes?
- Some of table fields store data from other languages so I'm using utf8_unicode_ci for them, should I not use it for fields that store only English and use utf8_general_ci instead.
- Should I use MyISAM instead of InnoDB if I don't need features such as row level locking etc.
10-11-12, 05:25 #2Registered User
- Join Date
- Sep 2009
- San Sebastian, Spain
1. Shortening the field names will slightly reduce the amount of network traffic. However, it will also make your SQL statements less readable. Personally I would use the longer versions of the field names so that I better understand what the code is doing.
2. Absolutely, a tinyint uses 1 byte of storage capacity. A varchar is dynamic and uses 1 byte to indicate the length of the content and 1 byte for the data. If anything use CHAR.
3. Partitioning helps if you need to manage large volumes of data. You need to make sure that your SQL statements access an individual partition otherwise it will perform the check against each partition which can take longer than having the data in a single table.
4. Indexes can give performance improvements on accessing the data. However, it has a side affect in that any time data is inserted, updated or deleted it needs to modify each of the indexes. This is equivalent to another update, delete or insert for each of the indexes. At the same time, an index on a very small table i.e. with 10 rows of 100bytes each will perform better without an index that with an index. So indexes can improve performance but you need to look closely at what fields you create the indexes on and whether they are really necessary.
5.You need to see the set of characters you will be writing to the database. For instance, in most cases latin1 is a perfectly suitable character set.
6. MyISAM and InnoDB differ not only on row level locking but also in terms of foreign key constraints and transaction handling. Also be aware that designing for MyISAM is will give better performance having everything defined as fixed length data types. This results in more storage capacity being used but quicker access. InnoDB on the other hand performs better using variable length datatypes.
There is no fixed rule for what is better. You need to look closely at the application you are developing and decide based on that.
10-11-12, 05:33 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
renaming a column makes no difference to the way the SQL engine process the query, granted it may take fractionally longer to send 'key_category_item_id' rather than 'cid', but once at the parser I'd expect the parser to use its own internal references. However you will always be better off using table and column names which are immediately obvious to understand what values they contain/represent. Some SQL engines have an inherent limit to the length of table/column names. sometimes these limits are hard enforced (ie you cannot put in more than x characters, sometimes they are soft enforced, you can put in as many characters as you like but the SQL engine only recognises the first n characters
use the appropriate datatype for the column for the type of data in that column. if its a number use one of the numerous numeric types, if its a date use a datetime, if its text use one of the appropriate string/'text datatypes.. if you use char(10) and varchar(10) and you are storing 10 characters then varchar will actually consume more space that char(10). varchar is best used when storing large strings whicvh are variable in length. although arguably in these days of multi terraybyte disks its arguable whether the trade off in using a varchar in say sub 100 character columns is worthwhile. using a varchar column in a MySQL table can impose limtations on data retrieval and indexing
indexing is always a thorny issue. more indexes means heavier overhead to write data on the server. it also may mean that your periodic maintenance / reindexing can take longer. carefull choice of indexes can speed retrieval / read of data. unless you see a need to index a column don't. but do make certain that columns used in where clauses, joins and so on are indexed. pay attention to the query plan (look at the EXPLAIN and also look at the slow query log
if you have some columns storing non English, then I'd suggest you make the chaneg and have all columns (where there is a reasonable expectation that non English may be used) capable of accepting non English symbols. Unless you KNOW with abosolute certainty that there will never be a need.
if you want to implement relational integrity in the middle tier and or presentation layer then MyISAM is fine. its supposed to be faster then INNODB. but personally I prefer sleeping easier at night knowing that relational integrity is enforced in the DB rather than relying on application developers, even if that developer is meI'd rather be riding on the Tiger 800 or the Norton
10-13-12, 12:46 #4Registered User
- Join Date
- Jul 2011
I appreciate both responses are quite useful. I have been reading about optimization techniques, the bottom line is that the biggest boost I get is by creating proper and careful indexes, then use queries that take maximum advantage of those indexes. Other things are minor as compared to indexing, not that they are not important.