… WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is worse as the statement started…, ie there is no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table… actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you can consider using –no-lock option with…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… KEY (id), KEY state_id (state_id) ) ENGINE=MyISAM; 4) Dictionary table for cities_join: CREATE TABLE IF NOT EXISTS `states` ( `id… it takes about 0.022 per query which makes it about 4 times faster than for MyISAM. This is great example of… see, ENUM and VARCHAR results are almost the same, but join query performance is 30% lower. Also note the times themselves – traversing…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… using MyISAM and Innodb tables. This time unlike other benchmarks I decided to do Join not on primary key and have query to… of response time. OK. Lets start with first simple MyISAM table and join query performed on INT fields: CREATE TABLE `intjoin` ( `i` int… executed the same query in 2.9 seconds which was a bit disappointing for me as I expected MyISAM to be slower…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… NULL, PRIMARY KEY (`id`), KEY `d` (`d`,`group_id`) ) ENGINE=MyISAM AUTO_INCREMENT=18007591 DEFAULT CHARSET=latin1 mysql> select sum(events… sec. The query with BETWEEN range replaced with IN list was instant 0.00 sec same as the query using join with day list table. So we finally managed to get better performance by joining data to yet…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
We had discussion today which involved benchmarks of Join speed for MyISAM and Innodb storage engines for CPU bound workload, this is… – primary key, integer indexed column and indexed char column. The query is also trivial – the point was to make sure it… MyISAM generally has lower processing overhead due to its simplicity MyISAM still a bit better by primary key join than for secondary key join…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… order to demonstrate the power of Shard-Query it is important to test complex queries involving joins and aggregation. A star schema is…. I should note that I did this preprocessing with the MyISAM storage engine, then I dumped the data to tab delimited… an example of such a query. . Query details: — Q1 SELECT DayOfWeek, count(*) AS c from ontime_fact JOIN dim_date using (date_id…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 3 Comments
… hack 
The fact MyISAM will by default compress string keys but not integer keys is the biggest reason joins using string keys… testing 4 types of queries: select count(*) from t1, t1 t2 where t1.c=t2.c – simple join by string column select… may give great space savings. Defaults for MyISAM are good for most cases Avoid joining on strings, it is much slower anyway…
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
…. However, take into account, that for MyISAM we need to choose proper indexes to execute queries effectively, and there is pain coming… amount of data TokuDB will be faster MyISAM. Now to queries. Bradley pointed me that query Q5 SELECT t.carrier, c, c2, c… WHERE DepDelay>10 AND Year=2007 GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 FROM ontime WHERE Year=2007…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
I have written before – MyISAM Does Not Scale, or it does quite well – two main … waiting on “efficient” Information Schema Query Execution (as you may guess we just need to join two previous results sets here) we… min 23.67 sec) A bit more query hacking and we get a query which will return statements to initialize key buffers…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…. COUNT Many people think counting is fast, some have heard MyISAM is instant with COUNT(*) without reading enough details to know… where clauses are using indexes, try to have queries index covered and avoid joins as much as possible. ORDER BY I already… sure for queries you’re going to run ORDER BY .. LIMIT can be performed using the index, for example simple JOIN is…
