Re: Joining memory tables is very very slow!
> I'm using MySQL 5.1.30 and have several memory tables with indexes on the > appropriate columns. When I try and join 2 particular memory tables together > to get 5k rows, it takes 90 seconds. > This is incredibly slow considering table1 has 11k rows and table2 has 5k > rows. A table join like this should take 10 ms. > > An explain shows it is not using and index for the table but Extra has > "Using where;Using join buffer". I can try and use "Force Index(..)" and it > still will not use the index. > > If I use the MyISAM table instead of that one particular memory table the > query takes 800ms which is reasonably fast. > > Is there a way to force it not to use the join buffer? > Has anyone else noticed the slow memory table joins in 5.1? > > TIA > Mike perhaps Memory tables or resulting tables are being converted into disk based table as MyISAM. it is slow due to file creation operations unlike myisam which is already disk based and no need to create files. virgil http://www.jampmark.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Joining memory tables is very very slow!
Memory tables use hash indexes by default instead of b-tree. Try changing the index, that should help significantly. regards, Walter On Tue, Mar 31, 2009 at 6:47 PM, mos wrote: > I'm using MySQL 5.1.30 and have several memory tables with indexes on the > appropriate columns. When I try and join 2 particular memory tables together > to get 5k rows, it takes 90 seconds. > This is incredibly slow considering table1 has 11k rows and table2 has 5k > rows. A table join like this should take 10 ms. > > An explain shows it is not using and index for the table but Extra has > "Using where;Using join buffer". I can try and use "Force Index(..)" and it > still will not use the index. > > If I use the MyISAM table instead of that one particular memory table the > query takes 800ms which is reasonably fast. > > Is there a way to force it not to use the join buffer? > Has anyone else noticed the slow memory table joins in 5.1? > > TIA > Mike > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Joining memory tables is very very slow!
I'm using MySQL 5.1.30 and have several memory tables with indexes on the appropriate columns. When I try and join 2 particular memory tables together to get 5k rows, it takes 90 seconds. This is incredibly slow considering table1 has 11k rows and table2 has 5k rows. A table join like this should take 10 ms. An explain shows it is not using and index for the table but Extra has "Using where;Using join buffer". I can try and use "Force Index(..)" and it still will not use the index. If I use the MyISAM table instead of that one particular memory table the query takes 800ms which is reasonably fast. Is there a way to force it not to use the join buffer? Has anyone else noticed the slow memory table joins in 5.1? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
