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.
4 Comments
Comments RSS
TrackBack Identifier URI
User contributed tags for this post:
mysql join performance (144) - mysql performance tips (86) - mysql count speed (82) - mysql union performance (62) - mysql count performance (52) - mysql count slow (44) - mysql joins performance (34) - mysql performance join (32) - mysql count(*) slow (32) - mysql multicore (27) - mysql count(*) performance (23) - wordpress mysql performance (21) - mysql performance count (21) - mysql join slow (20) - mysql large joins (20) - easy mysql (16) - mysql count(*) optimization (16) - mysql large table performance (16) - mysql performance union (15) - mysql join large tables (15) - mysql union slow (15) - mysql union speed (14) - mysql performance large tables (13) - mysql performance (12) - count(*) performance mysql (12) - MySQL large join (11) - join large tables mysql (11) - mysql large tables performance (11) - mysql join large table (11) - mysql multicore performance (10) - mysql count optimization (10) - mysql count(*) (10) - mysql big tables (9) - mysql join big tables (9) - mysql performance big tables (8) - mysql join speed (8) - mysql join tips (8) - optimize count(*) mysql (8) - mysql count union (8) - count mysql performance (8) - mysql where count(*) (8) - mysql count(*) count(1) (8) - mysql (7) - JOINs performance IN MYSQL (7) - mysql joining large tables (7) - mysql large table join (6) - mysql join optimization (6) - performance join mysql (6) - mysql query optimization tips (6) - mysql join table speed (6) - optimize mysql count (6) - optimize mysql union (6) - mysql performance joins (6) - mysql huge (6) - mysql multi core performance (6) - mysql speed up join (6) - mysql count(*) > 1 (6) - optimisation count(*) mysql (6) - COUNT(*) slow mysql (5) - wordpress mysql slow (5) - cache 63CG UrCLvcJ maisonbisson com blog post 11102 fee (5) - mysql performance on large tables (5) - mysql big tables performance (5) - mysql slow join (5) - count(*) mysql optimisation (5) - mysql count join (5) - mysql optimizar count(*) (5) - join mysql performance (5) - mysql join table performance (5) - mysql optimize count(*) (5) - mysql multi-core (5) - mysql count(*) optimize (5) - mysql count optimize (5) - mysql join count(*) (5) - mysql performance many columns (5) - count(*) speed mysql (5) - mysql performance where 1=1 (5) - mysql performance count(*) (5) - mysql speed count (5) - mysql optimize count (5) - mysql speed up count (5) - is a mysql union a bad idea (5) - join performance mysql (4) - mysql preformance (4) - mysql speedup (4) - count(1) mysql (4) - where count(*) mysql (4) - mysql join large table performance (4) - mysql speed up union (4) - mysql performance large table (4) - mysql where in performance (4) - mysql count optomise (4) - MYSQL QUERY PERFORMANCE (4) - COUNT(*) MYSQL JOIN (4) - optimizing mysql count(*) (4) - mysql large performance (4) - mysql slow query union (4) - mysql 4 performance (4) - tips to speed up queries mysql (4) - wordpress mysql speed (4) - mysql multi core (3) - performance (3) - mysql performance wordpress (3) - mysql tips column name simple (3) - mysql performance simple (3) - mysql join tables performance (3) - mysql query tips (3) - mysql join count (3) - mysql multi core optimization (3) - large table join performance (3) - mysql tips (3) - mysql count(*) > 1 (3) - mysql large tables (3) - mysql join 3 tables (3) - mysql join table is slow (3) - union mysql performance (3) - mysql where count(*) > (3) - mysql where count(*) > 1 (3) - mysql performance join large tables (3) - mysql speed tips (3) - count mysql optimize (3) - optimize mysql multicore (3) - mysql join huge tables (3) - mysql performance join where (3) - http://maisonbisson.com/blog/post/11796/ (3) - mysql huge joins (3) - mysql slow union (3) - mysql where COUNT(*) > 1 (3) - mysql speed (3) - speed multiple joins mysql (3) - mysql join count comments (3) - mysql speedup query (3) - optimize mysql count(*) (3) - mysql speedup count (3) - mysql 5 multi core optimization (3) - count(*) mysql optimization (3) - mysql count large table slow (3) - mysql large table joins (3) - mysql count(*)>1 (3) - mysql COUNT() speed (3) - joining 2 very big tables in mysql (3) - mysql one table vs many small tables (3) - speeding up count in mysql (3) - mysql union tips (3) - MYSQL count large table (3) - MySQL COUNT Large Tables (3) - mysql joins slow (3) - mysql query performance tips (3) - mysql 4 speed (3) -
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
true nick. sometimes * will be much faster like here. Small smaple shows index can be used for count and data does not have to be touched at all.
mysql> explain select timestamp,count(*) from log_entry where timestamp = 2454944;
+—-+————-+———–+——+—————+———–+———+——-+———+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———–+——+—————+———–+———+——-+———+————————–+
| 1 | SIMPLE | log_entry | ref | multi_one | multi_one | 5 | const | 1616103 | Using where; Using index |
+—-+————-+———–+——+—————+———–+———+——-+———+————————–+
1 row in set (0.00 sec)
mysql> explain select timestamp,count(id) from log_entry where timestamp = 2454944;
+—-+————-+———–+——+—————+———–+———+——-+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———–+——+—————+———–+———+——-+———+————-+
| 1 | SIMPLE | log_entry | ref | multi_one | multi_one | 5 | const | 1616103 | Using where |
+—-+————-+———–+——+—————+———–+———+——-+———+————-+
1 row in set (0.00 sec)
@Artur: Yeah, I got corrected pretty quickly after I posted this years ago.