Re: Joining memory tables is very very slow!

[Virgilio Quilario]

> 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!

[Walter Heck - OlinData.com]

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