What is Digg?Sponsored by Dragon Age: Origins
Follow the Dragon Age: Origins development team on Twitter view!
twitter.com/DragonAge - EA presents BioWare's new dark fantasy epic Dragon Age: Origins. '9/10' from Game Informer.
47 Comments
- rjpowell, on 10/12/2007, -0/+26maybe he should read "How not to optimize a MySQL query"
http://immike.net/blog/2007/04/09/how-not-to-optimize-a-mysql-query/ - codenexus, on 10/12/2007, -1/+9Funniest SQLesque saying I've found is on a ThinkGeek t-shirt...
SELECT * FROM users WHERE clue > 0;
0 rows returned
I found it here... http://www.thinkgeek.com/tshirts/coder/595d/
...funny, to me, because its true! - merreborn, on 10/12/2007, -0/+6That article really is a ***** of a lot better than this one.
- duniyadnd, on 10/12/2007, -0/+6I needed a half a page of tips to explain how to optimize mysql when I have an entire chapter at my disposal at the official site?
http://dev.mysql.com/doc/refman/5.0/en/optimization.html
People need to read more rather than just digg the next "top 10" list and learn optimize their database based on their personal needs.
Another page with good tips:
http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html - daybreaker, on 10/12/2007, -3/+9Yes! I'll get all sorts of chicks now!
- vann, on 10/12/2007, -0/+5My reply: http://20bits.com/2007/04/10/10-tips-for-optimizing-mysql-queries-that-dont-suck/
- claco, on 10/12/2007, -0/+5They forget a big one: don't use count(*) in InnoDB tables. Big speed killer.
- neospy, on 10/12/2007, -0/+510 Tips for Optimizing CAPITALIZATION. Read up on that one first.
- osbjmg, on 10/12/2007, -0/+411) Learn how relational databases work, instead of just taking tips from digg.
- snoonan77, on 10/12/2007, -1/+5Your assessment is dead on, SnakeO.
drop friend;
quit
rm -rf /
kill -9 1 - snoonan77, on 10/12/2007, -0/+3People are looking for good information, that's all. Can you follow up with some alternate links?
- zetsurin, on 10/12/2007, -0/+3Tip #1: Install PostgreSQL. Done.
- wbeavis, on 10/12/2007, -0/+3Indices are the key.
/see what I did there. - se7en11, on 10/12/2007, -0/+3The two biggest things are proper indexes and number of queries. If you can run 2 or 3 big queries all with proper indexes at the start, then everything should be fine. We had a site with just under 150,000 products that was taking like 2 min to complete a search (similar to digg :-P ). But once we got the indexes fixed, the site blazes and returns results in less than 3 seconds.
Also for what it's worth, try using the EXPLAIN in the front of your queries if they're running slow to see if it's using the proper indexes and what's causing it to run slow. --> http://dev.mysql.com/doc/refman/5.0/en/explain.html - vann, on 10/12/2007, -0/+2Ok, this list really sucks. In fact, I think I can do better: http://20bits.com/2007/04/10/10-tips-for-optimizing-mysql-queries-that-dont-suck/
Have a read. - rip747, on 10/12/2007, -1/+3wow.....
that was probably the dumbest article I read. If you're going to use DB specific functions and settings then they need to be in a stored procedure and not in an inline query. Also most of his point were the most basic crap I've ever heard of. I don't see why articles like this get dugg to death. I gues you put anything with an OS spin on digg and the fanboys go ape *****. - raccettura, on 10/12/2007, -0/+2This wasn't really much help... all the tips were pretty obvious. Perhaps claco's link is better.
- Lounger540, on 10/12/2007, -0/+2Examples or syntax would have been a little helpful.
- Habikki, on 10/12/2007, -0/+2@claco
Good catch on this. I was reading through this and was going to make a comment on it, then to my dismay I found that I had been beat to the punch!!! :)
I just took the MySQL 5.0 DBA Certification Exams last week and I didn't know this until I was reviewing, but the MyISAM Storage Engine is optimized to use the COUNT(*) on a table as it stores that count in the Header of the Table. InnoDB (really all the other storage engines that do not rely on MyISAM in the background) do not store the row count and must perform a table scan each time that it is queried. - SilverRocket, on 10/12/2007, -0/+2Oh my god, how can you miss the ONE optimization that can speed up your site instantly? Enable the MySQL Query Cache!!!!!!
- sirdaz, on 10/12/2007, -0/+1huh? Where does the IF statement come into this?
But using ELSE IF and END IF in an SQL query would just increase bandwidth, and also make the query less readable.
SELECT IF(result > 5, result, '-') AS res FROM bla ba...
Nice and easy to read :-) - TroubleInMind, on 10/12/2007, -0/+1Not so much goodness here.
- se7en11, on 10/12/2007, -0/+1I'm pretty sure count(*) would be slower on any table type. All tables should have some kind of primary key, so just do a count on it.
- cpoteet, on 10/12/2007, -1/+2Using stored procedures should also be on that list. Now available in MySQL.
- claco, on 10/12/2007, -0/+1I found this useful as well: http://jpipes.com/presentations/mysql_perf_tuning.pdf
- GrendelT, on 10/12/2007, -0/+1Right on. I was hoping someone else was catching this one.
- kewlceo, on 10/12/2007, -0/+1Yours I dugg. As for the one at the top of this page...buried.
- munky100, on 10/12/2007, -0/+1Number 1 and number 9 look quite similar...
- funk49, on 10/12/2007, -0/+1One of the best that I've seen lately (and one that saved my ass) was using the "slow_query" parameter within mysql.cnf. It helped find a query that was searching 300K records...one that was bottlenecking the system. Now the system is blazing. I can't stress indices and this slow_query tool enough.
- batv0r, on 10/12/2007, -0/+1Looks like a ripoff of http://forge.mysql.com/wiki/Top10SQLPerformanceTips
- dchesterton, on 10/12/2007, -0/+1Actually MyISAM tables are extremely fast at returning count()s. It stores it in memory so even a million row+ table will return it almost instantly.
- developertest, on 10/12/2007, -0/+0Point 9 is the summary of point 1
- developertest, on 10/12/2007, -0/+0yes really nice
- Habikki, on 10/12/2007, -0/+0The first link is a great resource, and although the second still has some merit... it's also written for a conference that occurred in 2000 (still references MySQL 3.x "Benefits of using MySQL 3.23' :)).
- inactive, on 10/12/2007, -1/+1Oh, come on, the If/Then thing isn't fair - I blame programming for that. But then again, I also blame programming for the fact that whenever I pose an if statement, I use else if there are other options, and I always finish with an end if. Needless to say, sometimes my friends have terrible difficulties understanding me.
- claco, on 10/12/2007, -2/+1Actually, count(*) is slow on InnoDB even on an indexed column.
- boxxa, on 10/12/2007, -1/+0I love it
- developertest, on 10/12/2007, -1/+0This my same test replay, please ignore this
- developertest, on 10/12/2007, -1/+0Nice to improve the performance, it is very helpful for me. :) x
- developertest, on 10/12/2007, -1/+0The is test replay for my comment please ignore.
- kirbs, on 10/12/2007, -4/+2snakeO ftw
- SnakeO, on 10/12/2007, -9/+7You replace your best friends rather easily with one you hardly know...
You sir are no friend at all. - lakindc, on 10/12/2007, -2/+0I don't think they are faster in MySQL. . .In fact I think they are slower. . .Using stored procedures on other platforms (like MS SQL Server) will speed them up. . .But this is not the case with MySQL.
http://forums.mysql.com/read.php?98,63254,63423#msg-63423 - FairlyStupid, on 10/12/2007, -5/+1"INSERT INTO", when optimized, makes it go faster and the result comes fast.
- pgup, on 10/12/2007, -7/+2Bookmarked
- kirbs, on 10/12/2007, -9/+1database, noOOOOOOOOOOOooooooooooooooOOOOOOOOOOOOOOOOOoooooooooooooooOOOOOOOOOOOOOOOoooooooooooooo
- snoonan77, on 10/12/2007, -18/+7DELETE FROM friend WHERE type='best';
INSERT INTO friend (name, type) VALUES ('jlabs', 'best');
The Digg Toolbar for Firefox lets you Digg, submit content, and keep track of Digg even when you're not on the Digg site. Download the official 
© Digg Inc. 2009
— Content created and posted by Digg users is