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