here is query:
SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image, nw.status, src.title as src_title, src.keyword as src_keyword, src.url as src_url, src.icon as src_icon, cat.title as cat_title FROM news as nw INNER JOIN sources as src ON nw.source=src.source_id INNER JOIN categories as cat ON nw.category=cat.category_id WHERE nw.status=1 ORDER BY nw.news_id DESC LIMIT 0,140;
news table size is around 30MB. server RAM is 1GB.
[mysqld] max_connections = 120 key_buffer = 32M myisam_sort_buffer_size = 32M join_buffer_size = 8M read_buffer_size = 8M sort_buffer_size = 32M table_cache = 1024 thread_cache_size = 286 interactive_timeout = 25 wait_timeout = 1000 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 8K query_cache_size = 32K query_cache_type = 1 tmp_table_size = 16M [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M local-infile=0
it was myisam I converted to innodb for better performance but nothing changed so I get back to myisam.
query works correctly on localhost but when ran in server, get high load and eat all RAM until crash mysql. just one of above query get me highload! something like that mysql could not finish it. table has around 50000 records.
I tried to fix issue by changing my.cnf parammeters with no luck.