Yes, I’m still trying to squeeze more performance out of MySQL. And since small changes to a query can make a big difference in performance…
Here are two really easy things to be aware of:
- Never do a
COUNT(*) (or anything *, says Zach). Instead, replace the * with the name of the column you’re searching against (and is hopefully indexed). That way some queries can execute entirely in the keycache (while * forces MySQL to read every matching row from the table).
- When joining two large tables, but only searching against one, put the join statement at the end. Why join the two entire tables when you only have to join the matching rows?
I mention these because, well, I’ve known them forever, but upon seeing them again I realized I hadn’t really obeyed those simple rules in some of my queries.
Separately, there’s some pretty good info on what server variables affect what at mysqlperformanceblog too.
mysql, optimization, query optimization, performance, tips
Related:
Posted June 5, 2007 by Casey
Categories: Technology. Tags: mysql, optimization, performance, query optimization, tips.
2 Comments
Comments RSS
TrackBack Identifier URI
Leave a comment
User contributed tags for this post:
mysql join performance (37) - mysql performance tips (30) - mysql union performance (17) - mysql count performance (15) - mysql joins performance (15) - easy mysql (14) - mysql join slow (13) - mysql performance join (12) - mysql performance (11) - mysql count(*) slow (11) - mysql performance big tables (7) - mysql big tables (7) - mysql count speed (7) - mysql large tables performance (7) - mysql large table performance (6) - mysql performance union (6) - cache 63CG UrCLvcJ maisonbisson com blog post 11102 fee (5) - mysql performance count (5) - join large tables mysql (5) - mysql large joins (5) - mysql join speed (5) - mysql join table speed (5) - mysql multi-core (5) - count(*) mysql optimisation (5) - MySQL large join (5) - mysql (5) - mysql performance large tables (5) - mysql huge (5) - mysql count optomise (4) - mysql slow join (4) - mysql count(*) performance (4) - optimize count(*) mysql (4) - mysql optimize count(*) (4) - count(1) mysql (4) - mysql count join (4) - optimize mysql count (4) - mysql count slow (4) - sex300 com (4) - COUNT(*) MYSQL JOIN (4) - mysql union slow (3) - mysql query optimization tips (3) - mysql huge joins (3) - mysql where count(*) > (3) - mysql join table is slow (3) - mysql big tables performance (3) - mysql speedup query (3) - mysql join tables performance (3) - mysql join big tables (3) - count(*) performance mysql (3) - large table join performance (3) - mysql count(*) > 1 (3) - mysql performance join large tables (3) - mysql speedup (3) - mysql optimizar count(*) (3) - mysql count(*) optimization (3) - mysql where count(*) > 1 (3) - mysql join tips (3) - mysql speed up join (3) - speed multiple joins mysql (3) - mysql tips (3) - http://maisonbisson.com/blog/post/11796/ (3) - JOINs performance IN MYSQL (3) - mysql join optimization (3) - mysql performance on large tables (3) - mysql large table join (3) - mysql tips column name simple (3) - mysql multicore (3) - wordpress mysql performance (3) - join mysql performance (3) - mysql multicore performance (2) - large tables slow in mysql (2) - mysql large table optimization (2) - mysql optimize speed (2) - MySQL Query Optimization torrent (2) - MySQL perofmance (2) - speedup mysql (2) - mysql join where performance (2) - mysql WHERE (2) - optimize mysql union (2) - Speed Joins MySQL One or Many queries (2) - mysql performance test (2) - speeding up joins large tables mysql (2) - mysql join large tables (2) - mysql join count (2) - mysql join queries and performance (2) - count() mysql performance (2) - mysql large many tables vs (2) - mysql big-tables (2) - mysql speedup count (2) - mysql small tables vs big table (2) - optimization performance mysql (2) - mysql speed up (2) - mysql optimization (2) - mysql large table (2) - mysql optimize join large tables (2) - mysql optimize count queries (2) - COUNT(*) slow mysql (2) - mysql querying large tables (2) - join tables performance mysql (2) - mysql join 3 big tables (2) - mysql join 3 tables (2) - mysql where COUNT(*) > 1 (2) - mysql large tables (2) - union mysql performance (2) - wootee tip video (2) - easy sex com (2) - mysql big table (2) - mysql count(*) optimize (2) - wordpress mysql optimization (2) - mysql query optimization (2) - mysql optimization tips (2) - mysql speed (2) - query optimization (2) - optimization mysql speed (2) - MySQL large tables vs many tables (2) - mysql optimize count large table (2) - mysql more one join preformance (2) - mysql join query performance (2) - mysql speed two tables verses one (2) - MySQL Where 1=1 performance (2) - easy mysql joins (2) - mysql performance optimization large website (2) - mysql two count() (2) - where count(*) mysql (2) - time passing quote (2) - performance mysql large tables (2) - mysql performance many columns one table (2) - mysql joining big tables slow performance (2) - mysql join large table (2) - mysql performance joins (2) - mysql join statement (2) - performance of query in mysql (2) - mysql query to join two large tables quickly (2) - mysql join versus where performance (2) - count mysql optimize (2) - mysql large website tips (2) - mysql multicore mac (2) - optimize mysql count(*) (2) - MYSQL QUERY PERFORMANCE (2) - tips about mysql (1) - mysql performance on large table (1) - speed join table mysql (1) - mysql query optimization TIPS select union (1) - Optimise MYSQL Union (1) - 3 small queries or one big one mysql (1) - mysql join table large (1) - mysql 4 union query optimization (1) - mysql where 1'='1 (1) - MySQL multiple joins performances problems (1) - performance join (1) -
Good
Unfortunately you are incorrect with regards to count(*); Zach’s article is correct.
Why? count(*) is optimised by MySQL. The table descriptor is used without the need to read any table records in the case of count star with no where condition, and an index is used if possible if there is a where condition. Doing a count on an appropriately indexed column is fine too, as Zach notes, however there is the risk that should the choice of indices be changed in the future that the counted column is no longer optimal and there is then a big hit that may go unnoticed. So count(*) SHOULD be used.
It is fair to say that selecting of * is generally a bad idea for queries that return rows as most often then returns more data than is needed, taking more time in the process. For non performance critical queries using * is arguably OK and has advantages of reduced maintenance.
Using * during early stages of development may be justified on the basis that it may speed development if the database schema is still in flux. Doing so avoids the need to revise queries changing column names, deleting unused columns and adding new columns or ones that were forgotten. Once stable and before an application is released and finally tested, a code review should tidy up the queries.
Nick