MySQL queries are taking very long time to execute
Hello All,
I am having the issue with MySQL db, while we are fetching the data from multiple tables using joins.
We have 10 tables and we are generating the reports from the data from those 10 tables and the time taking for getting the results is taking very long time more than a minute even the number of records are 1000 and taking more time if number of records were increased.
Have tried using views and also by writing the normal queries, the time taken for view is 50% more than running a normal query. Even used the indexes as well.
Here is the configuration of MYSQL in my server.
Kindly suggest whether this configuration required any changes
[mysqld]
performance-schema=1
query_cache_size=100M
key_buffer_size=8M
sort_buffer_size=256k
read_buffer_size=256k
tmp_table_size=256M
max_heap_table_size=256M
table_definition_cache=8192
open_files_limit=50000
max_connections=1000
join_buffer_size=256k
symbolic-links = 0
query_cache_type=2
max_user_connections=250
query_cache_limit=10M
long_query_time=20
slow_query_log=1
slow_query_log_file="/var/log/mysql/log-slow-queries.log"
log-queries-not-using-indexes
collation_server=utf8_unicode_ci
character_set_server=utf8
# delayed_insert_timeout=40
interactive_timeout=30
wait_timeout=3600
connect_timeout=60
thread_cache_size=64
max_connect_errors=20
max_allowed_packet= 1073741824
read_rnd_buffer_size=2M ## 1MB for every 1GB of RAM
myisam_sort_buffer_size=32M
default-storage-engine=MyISAM
innodb_file_per_table=1
log-error=/var/log/mysqld.log
innodb_buffer_pool_size=13G
innodb_log_file_size=16M
sql_mode=IGNORE_SPACE,NO_ENGINE_SUBSTITUTION
secure_file_priv=""
-
Do you use cloud linux? 0 -
You could try adding EXPLAIN before your query (you'd need to run from MysQL command line) and MysQL will tell exactly how it's going to execute the query, what indexes it's going to use etc. MySQL may not really be using the indexes you think it is. 0
Please sign in to leave a comment.
Comments
3 comments