… left by the given user ? mysql> select… and using MySQL VIEWs ? mysql> create view user…create a very artificial query which will JOIN 2 views just to see how indexes are used: mysql…join” used to to join between them. In this particular case it is not that bad because “join cache” is used to perform…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… joined to get query result. If you’re executing count(*) queries for such result sets MySQL will perform the join even if you use LEFT JOIN… you will usually have several of these so performance improvements can be even higher. CREATE TABLE `fact` ( `i` int(10) unsigned NOT…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… with first simple MyISAM table and join query performed on INT fields: CREATE TABLE `intjoin` ( `i` int(10) unsigned…utf8 mysql> select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin…results are likely to be different as longer indexes expected to have much worse cache fit, …
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 3 Comments
… it for left or right half. For compressed block this is not going to work and MySQL will …Let us try couple of benchmarks now. I’ve created simple table with 100.000 rows with columns. … large memory areas. Reverse index scan performance got some 8 times faster and join on string value is almost …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…performance counters, statistics, information about transaction processing and all kinds of other things. In MySQL 5 number of Innodb performance…index. You can also see number of hash index lookups and number of non-hash index lookups which is indication of hash index…
