Not seeing the speed increase I expected from memory

[David Young]

Hi,

Here's the setup: 2 identical tables, one using MyISAM, and one in memory. Each tables contains 100 columns of BIGINT and was populated with 1 million rows of randomly generated numbers.

I have a test program to generate test queries that generate a random number and bitwise-AND on each column. For example:

SELECT count(*) FROM [table_name] WHERE
col_1 & [rand_num_1] > 0
AND col_2 & [rand_num_2] > 0
...
AND col_100 & [rand_num_100] > 0;

When I run the query on both tables, it'll take about 18 seconds for table on disk, and 16 seconds for table in memory. That seems to be very little performance gain to me.

Also, I tried query only 1 columns, 2, column, 3 columns, and so on. I still see only 1-2 second difference in query time, but each column I add increases the query time by 0.15 seconds. That's a bit surprisingly expensive, no?

Anyone can see what I'm doing wrong? Or this is the expected results for MySQL?

Thanks.