We had discussion today which involved benchmarks of Join speed for MyISAM and Innodb storage engines for CPU bound workload, … generally highly optimized Innodb builds hash indexes which helps to speed up lookup by indexes by passing BTREE index and using… which matches the index, so there is no need for MySQL to request next row matching index Note: This applies to…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary… is used to join the two tables? A: The question in this case is how MySQL will execute the join. If it will… on vendor, will the index have any impact on the speed of the query? A: If you extend the index from…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
… years is change of TIMESTAMP output format in MySQL 4.1 or changing JOIN evaluation in 5.0 (which could both cause different result set or make query not runnable in MySQL 5… of queries with good speed is not enough. You also need to perform stress testing to ensure both MySQL does not crash…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… in random places – this may affect index scan/range scan speed dramatically. The rows referenced by indexes also could be located… in MySQL is lack of advanced join methods at this point (the work is on a way) – MySQL can’t do hash join or sort merge join – it…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… disk problem, I pointed the MySQL 5.6.10 at the MySQL 5.5.30 data directory. Tthe speed was essentially the same… the join in both directions. Explain for query Q1.1: mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from lineorder join… from joining the tables in the other direction mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join lineorder…
Comment: Using CHAR keys for joins, how much is the overhead ?
June 19, 2007 By 18 Comments
… someone in MySQL may want to profile it in more details. Changing column types from INT to BIGINT slowed down join speed for…. Now for SHA1 based joins I also tested few storage options. First one is LATIN1 which improved join speed from 6.1 to… my “short” varchar join tests for Innodb table with utf8 encoding. This caused some degradation slowing down join speed from 4.5 seconds…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… to be 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 so it is not needed which…. To get better performance you can “Help” MySQL and remove JOIN for count(*) and do JOIN after limiting result set for retrieval queries…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… time In MySQL 5.0 with addition of greedy join the problem on looking at too many table join combinations in joins with very… applied. The other reason for difference is join_buffer which reduces number of passes MySQL had to perform and so number of… table scan of table in memory may be done at speed of millions of rows per second, while retrieval of large…
Post: Realtime stats to pay attention to in Percona XtraDB Cluster and Galera
November 26, 2012 By 4 Comments
… # of the cluster configuration. This changes every time a node joins or leaves the cluster. Seeing high values here may indicate… tells you if multi-threaded replication could help improve replication speed at all. Conflct Replication conflicts, as described in my last… fact, this is a sysbench test what is running full speed (these are VMs, so that’s not that particularly fast…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against… in ( select distinct co.Code from Country as co inner join CountryLanguage as cl on cl.CountryCode = co.Code where lower… join CountryLanguage as cl on cl.CountryCode = co.Code where lower(cl.Language) = ‘English’ and co.Code = c; > return res; > end// mysql…
