Re: Query with > 500000 entities in in where clause crashes server

[Jens Bijell]

Hi Rick,

thanks for taking my assumptions into consideration.

Indeed. In my opinion the value key_buffer_size=8G is too high.
It is recommended to set it to 25% of RAM max.

I also reviewed your suggested parameters:
-the following two are only needed when using in_memory_tables. This isn't the fact - or are there certain positive side effects for myisam ...?
max_heap_table_size 16777216
tmp_table_size 33554432

The next one myisam_sort_buffer_size 36700160
is for when doing index creation, alter table or repairing a table?

I will suggest to create 1 of those 'best' indexes first - trying to evaluate the best ordering for the key definitions according to the query.

If the 'between' statement on the date field can't be avoided:
Did I understand it right that the query breaks at that 'index position' where the Date column is defined? Then it should be placed at the end of that composite key ... or is it also an alternative creating a single/extra index for that date field.

There is a fast SAN underneath.

Those queries are generated by a Wizard using different filters which can't handle so called 'Placement Positions' yet - as I just found out - instead creating those huge lists and using group by.
This should also be changed to use distinct - if possible.

Oh yeah - there is a lot of work to do.

Thank you!

Best Regards,
Jens



Edited 1 time(s). Last edit at 06/17/2010 10:14AM by Jens Bijell.