5 Tips for Optimizing Mysql
Mysql is the most popular database system used with LAMP (Linux Apache Mysql PHP) architecture. Although as your database grows big, you need to optimize mysql for maximum performance. Here are 5 tips which we have learned while developing sukip and handling many data related issues with it.
1. Do your homework well
The very first thing you should do is turn on the MySQL slow query log and install mtop. This will give you access to information about the absolute worst offenders. Have a ten-second query ruining your web application? These guys will show you the query right off.
After you’ve identified the slow queries you should learn about the MySQL internal tools, like EXPLAIN, SHOW STATUS, and SHOW PROCESSLIST. These will tell you what resources are being spent where, and what side effects your queries are having, e.g., whether your heinous triple-join subselect query is sorting in memory or on disk. Of course, you should also be using your usual array of command-line profiling tools like top, procinfo, vmstat, etc. to get more general system performance information.
2. Schema Optimization
Before you even start writing queries you have to design a schema. Remember that the memory requirements for a table are going to be around (entries * size of a row). Unless you expect every person on the planet to register 2.8 trillion times on your website you do not in fact need to make your user_id column a BIGINT. Likewise, if a text field will always be a fixed length (e.g., a US zipcode, which always has a canonical representation of the form “XXXXX-XXXX”) then a VARCHAR declaration just adds a superfluous byte for every row.
Some people poo-poo database normalization, saying it produces unecessarily complex schema. However, proper normalization results in a minimization of redundant data. Fundamentally that means a smaller overall footprint at the cost of performance — the usual performance/memory tradeoff found everywhere in computer science. The best approach, IMO, is to normalize first and denormalize where performance demands it. Your schema will be more logical and you won’t be optimizing prematurely.
3. Partition your tables
Often you have a table in which only a few columns are accessed frequently. On a blog, for example, one might display entry titles in many places (e.g., a list of recent posts) but only ever display teasers or the full post bodies once on a given page. Horizontal vertical partitioning helps.
The above represents a situation where one is optimizing for reading. Frequently accessed data is kept in one table while infrequently accessed data is kept in another. Since the data is now partitioned the infrequently access data takes up less memory. You can also optimize for writing: frequently changed data can be kept in one table, while infrequently changed data can be kept in another. This allows more efficient caching since MySQL no longer needs to expire the cache for data which probably hasn’t changed.
4. Indexes
Often your choice of indices will make or break your database. For those who haven’t progressed this far in their database studies, an index is a sort of hash. If we issue the query SELECT * FROM users WHERE last_name = ‘Goldstein’ and last_name has no index then your DBMS must scan every row of the table and compare it to the string ‘Goldstein.’ An index is usually a B-tree (though there are other options) which speeds up this comparison considerably.
You should probably create indices for any field on which you are selecting, grouping, ordering, or joining. Obviously each index requires space proportional to the number of rows in your table, so too many indices winds up taking more memory. You also incur a performance hit on write operations, since every write now requires that the corresponding index be updated. There is a balance point which you can uncover by profiling your code. This varies from system to system and implementation to implementation.
5. SQL is not C
C is the canonical procedural programming language and the greatest pitfall for a programmer looking to show off his database is that he fails to realize that SQL is not procedural (nor is it functional or object-oriented, for that matter). Rather than thinking in terms of data and operations on data one must think of sets of data and relationships among those sets. This usually crops up with the improper use of a subquery:
-
SELECT a.id,
-
(SELECT MAX(created)
-
FROM posts
-
WHERE author_id = a.id)
-
AS latest_post
-
FROM authors a
Since this subquery is correlated, i.e., references a table in the outer query, one should convert the subquery to a join.
-
SELECT a.id, MAX(p.created) AS latest_post
-
FROM authors a
-
INNER JOIN posts p
-
ON (a.id = p.author_id)
- GROUP BY a.id
all cellular free ringtones one forfree 100 real ringtones eminemcom v motorola ringtone 180kingdom 2 hearts ringtone downloadringtones 3595 nokia6020 ringtone nokia6030 nokia ringtonemen good ringtones a few Map
mp3 aah tchare aayee aayee khushi mp3 remp3 matchbox 3amaasai mp3 aasairieu mp3 aandremp3 aalbum aalishaaahista mp3 aahistaaayega aanewala mp3 Map
movie hell highway tomovie of history makingmovies sex wife homemadespot hot moviemovie hot teenillegal porn moviesinspector movie gadgetinteracial movies sample Map
glory movie roadguess poster who moviemovies teen masturbation hairymovies fucking harcoremovie porn hardcumshot hardcore movies freemovie hentai listhentai movies download Map
loan minnesota cash advanceadvance mn loan cashadvance payday money online instant loanloan money adverse bad mortgage creditpersonal credit loan unsecured credit adverseunsecured adverse us credit loansecured adverse loan statusinterest loan mortgage only advice Map
ringtone airhornfree tracfone ringtone motorola 120tsch ringtone samsung a850ringtone alex t party mobilecricket audiovox ringtone 8900stanlet advert acrington730i ringtonesringtone composable free 5210 nokia Map
download treo ringtone 600a800 ringtone samsungnokia ringtone 3310 video freeactual ringtone songpolyphonic ringtones american free download100 ringtones logos sms sent freenokia 3560 ringtone free polyphonicamanda nokia ringtone Map
eligibility 2007 credits new taxtest school high accredited equivalencyamerican credit ma managementcredit subject cards accept posts directproducer add movies powerpoint to creditscanada accreditation 911on-line courses accreditedexpress miles card american credit air Map
one buy link viagra 80802005 blog comment spam wordpress viagracialis casino spam viagra 2006 blog180 ct cod delivery tramadolsildenafil viagra substitute cheapest 5properties tramadol addiction ofinurl hcl 50 mg tramadolamount xanax overdose Map
movie the glorynudes moviebbw free porn moviesfull length movies sexgirls of stripping moviesscenes movies from sexstilemedia moviesbittorrent movies Map
loan calculator pay offloan payment calculatloan payoffsloan portholequarters loan missourirates on ag minnesota loans loanloan repayment formulaloan rescue Map