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
Posted June 5, 2007 by Casey Bisson
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 (43) - mysql performance tips (30) - mysql union performance (28) - mysql count performance (18) - mysql joins performance (16) - mysql performance join (15) - mysql join slow (15) - mysql count speed (14) - easy mysql (14) - mysql count(*) slow (12) - mysql count(*) performance (11) - mysql performance (11) - mysql count slow (10) - mysql performance large tables (9) - mysql large tables performance (8) - mysql performance union (8) - mysql large joins (7) - mysql performance big tables (7) - MySQL large join (7) - count(*) performance mysql (7) - mysql big tables (7) - join large tables mysql (7) - mysql huge (6) - mysql join table speed (6) - mysql count(*) optimization (6) - mysql performance count (6) - mysql large table performance (6) - mysql join speed (6) - mysql (6) - mysql join big tables (5) - mysql multi-core (5) - count(*) mysql optimisation (5) - mysql multicore (5) - wordpress mysql performance (5) - mysql union slow (5) - cache 63CG UrCLvcJ maisonbisson com blog post 11102 fee (5) - mysql big tables performance (4) - mysql join large tables (4) - mysql optimize count(*) (4) - MYSQL QUERY PERFORMANCE (4) - count(1) mysql (4) - optimize mysql count (4) - optimize mysql union (4) - mysql join tips (4) - mysql slow join (4) - performance join mysql (4) - mysql count optomise (4) - sex300 com (4) - COUNT(*) MYSQL JOIN (4) - optimize count(*) mysql (4) - mysql join optimization (4) - mysql count join (4) - mysql join tables performance (3) - mysql performance join large tables (3) - join mysql performance (3) - large table join performance (3) - mysql large tables (3) - mysql query tips (3) - speed multiple joins mysql (3) - JOINs performance IN MYSQL (3) - mysql speed up join (3) - mysql join count comments (3) - mysql tips (3) - mysql count large table slow (3) - mysql query optimization tips (3) - where count(*) mysql (3) - mysql count(*) > 1 (3) - http://maisonbisson.com/blog/post/11796/ (3) - mysql speedup query (3) - mysql tips column name simple (3) - mysql performance on large tables (3) - mysql where count(*) > 1 (3) - mysql speedup (3) - mysql large table join (3) - mysql huge joins (3) - count mysql optimize (3) - mysql joining large tables (3) - count(*) speed mysql (3) - mysql 5 multi core optimization (3) - mysql where count(*) > (3) - mysql join large table (3) - mysql optimizar count(*) (3) - mysql join table is slow (3) - mysql large table optimization (2) - performing two counts in one query mysql (2) - MySQL Query Optimization torrent (2) - mysql join where performance (2) - mysql two count() (2) - mysql union performance problem (2) - mysql performance count(*) (2) - mysql count query speed up (2) - mysql large many tables vs (2) - mysql optimize count queries (2) - mysql join queries and performance (2) - optimize mysql count(*) (2) - large tables slow in mysql (2) - wordpress mysql preformance (2) - speeding up joins large tables mysql (2) - mysql multicore mac (2) - mysql small queries vs join (2) - mysql join versus where performance (2) - mysql speedup count (2) - easy.sex.com (2) - wootee tip video (2) - mysql join query performance (2) - mysql preformance count (2) - mysql optimize count large table (2) - performance of query in mysql (2) - mysql join 3 tables (2) - optimization mysql speed (2) - mysql large table (2) - mysql, join, advantages (2) - join tables performance mysql (2) - mysql query to join two large tables quickly (2) - mysql querying large tables (2) - mysql more one join preformance (2) - mysql performance many columns (2) - mysql speed up (2) - mysql big tables joins (2) - union mysql performance (2) - mysql table performance (2) - MySQL Where 1=1 performance (2) - mysql performance many columns one table (2) - mysql joining many tables performance (2) - performance mysql large tables (2) - mysql large website tips (2) - mysql optimization tips (2) - mysql optimize speed (2) - mysql count optimization (2) - mysql where count(*) (2) - performance of joins in mysql (2) - mysql WHERE (2) - mysql where COUNT(*) > 1 (2) - MySQL large tables vs many tables (2) - Speed Joins MySQL One or Many queries (2) - mysql join 3 big tables (2) - count() mysql performance (2) - mysql joining big tables slow performance (2) - speedup mysql (2) - optimization performance mysql (2) - mysql performance optimization large website (2) - tips to optimize mysql performance (2) - mysql speed up union (2) - time passing quote (2) - easy mysql joins (2) - mysql count(*) optimize (2) - mysql preformance (2) - MySQL perofmance (2) - performing joins mysql (2) - easy sex com (2) -
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