I don't know why MySQL query is too slow with status "Sending data". I read many questions and found that this status comes when you have slow disk I/O. But I have a good server hosting with 500GB Hard disk and 8GB ram on my server. I am also using indexes on the required columns on each table.
My query with left join:
Select tbl_songs.id, tbl_songs.song_title, tbl_songs.picture,
tbl_songs.description, tbl_songs.song_status, tbl_songs.latest,
tbl_songs.popularity, tbl_songs.posted_date, tbl_songs.ranking_order,
tbl_artists.id as artist_id
from tbl_songs
left join tbl_songs_artist ON tbl_songs_artist.song_id=tbl_songs.id
left join tbl_artists ON tbl_songs_artist.artist_id=tbl_artists.id
where MATCH (tbl_artists.artist_name)
AGAINST ('Vectro Electro' IN BOOLEAN MODE)
ORDER BY tbl_songs.ids ASC
limit 15
Here are few stats below on my server:
free -mh
total used free shared buffers cached
Mem: 7.8G 7.7G 123M 38M 88M 7.0G
-/+ buffers/cache: 617M 7.2G
Swap: 4.0G 33M 4.0G
top - 13:58:57 up 32 days, 9:43, 2 users, load average: 2.07, 2.23, 1.76
Tasks: 133 total, 1 running, 132 sleeping, 0 stopped, 0 zombie
%Cpu(s): 10.6 us, 5.8 sy, 0.0 ni, 75.2 id, 8.4 wa, 0.0 hi, 0.1 si, 0.0 st
KiB Mem: 8176780 total, 8038168 used, 138612 free, 1200 buffers
KiB Swap: 4194300 total, 40028 used, 4154272 free. 7424124 cached Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7683 mysql 20 0 1051368 186976 2984 S 65.8 2.3 161:18.73 mysqld
4946 root 20 0 104564 3972 2884 S 0.7 0.0 0:00.08 sshd
52 root 20 0 0 0 0 S 0.3 0.0 26:08.48 kswapd0
MySQL global variables are:
#tmpdir = /tmp
tmpdir = /var/mysqltmp
max_heap_table_size = 512M
tmp_table_size = 512M
#sort_buffer_size = 218M
#join_buffer_size = 5G
#read_buffer_size = 5G
#myisam_sort_buffer_size = 218M
#innodb_buffer_pool_size=2G
#
#key_buffer = 218M
#max_allowed_packet = 218M
thread_stack = 192K
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 0
query_cache_type = 0
[mysqldump]
quick
quote-names
#max_allowed_packet = 218M
[isamchk]
#key_buffer = 218M
EXPLAIN EXTENDED
command - try and use that to see if there are any missing indexes. I assume that fields with tbl_name.id arePK
s and that fields with_id
are also indexed but just in case. Do aSHOW CREATE TABLE tablename\G
for all tables and post them. Agree with @GerardH.Pille - why useLEFT JOINS
- justJOIN
should do! Finally, I would recommend that you useALL CAPS
for your SQL keywords and lowercase for tablenames (as you have) - much more legible that way! – Vérace Apr 22 '18 at 7:26