0

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
Improve this question
2
  • Don't look at the disks, look at the query. Try inner iso left joins. – Gerard H. Pille Apr 22 '18 at 5:00
  • 1
    MySQL has an EXPLAIN EXTENDED command - try and use that to see if there are any missing indexes. I assume that fields with tbl_name.id are PKs and that fields with _id are also indexed but just in case. Do a SHOW CREATE TABLE tablename\G for all tables and post them. Agree with @GerardH.Pille - why use LEFT JOINS - just JOIN should do! Finally, I would recommend that you use ALL 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
0

Change LEFT JOIN to JOIN.

Change

AGAINST ('Vectro Electro' IN BOOLEAN MODE)

to

AGAINST ('+Vectro +Electro' IN BOOLEAN MODE)

Have this index on tbl_artists: FULLTEXT(artist_name).

I assume that tbl_songs has PRIMARY KEY(id).

Assuming that tbl_songs_artist is a many-to-many mapping table, follow the tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Is this a typo? tbl_songs.ids

Use ENGINE=InnoDB.

Don't comment out the setting for innodb_buffer_pool_size.

Not more than 1% of RAM for these:

max_heap_table_size
tmp_table_size

("Sending status" is useless info.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.