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