Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    New Coder
    Join Date
    Jan 2009
    Posts
    34
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Copying to tmp table on disk

    I am running this query in a php script and it is taking a long time to get the results.

    Code:
    SELECT Tracks.id, Album.idAlbum AS idAlbum, Track_Artist.idTrack_Artist AS idTrack_Artist, Tracks.file_location_hash, Tracks.file_path, Tracks.file_name, Tracks.sample_rate, Tracks.`year` , Tracks.title, Tracks.track_number, Tracks.bpm, Tracks.duration, Tracks.extension, Tracks.scan_date AS date_added, Genres.Name AS genres_name, Artist.Name AS artist_name, Album.Name AS album_name
    FROM etracks.Tracks
    LEFT OUTER JOIN etracks.Track_Genres ON Track_Genres.idTrack = Tracks.id
    LEFT OUTER JOIN etracks.Genres ON Track_Genres.idGenres = Genres.idGenres
    LEFT OUTER JOIN etracks.Track_Artist ON Track_Artist.idTrack = Tracks.id
    LEFT OUTER JOIN etracks.Artist ON Track_Artist.idArtist = Artist.idArtist
    INNER JOIN etracks.Album ON Tracks.album_id = Album.idAlbum
    WHERE `title` != ''
    GROUP BY Tracks.id
    ORDER BY Tracks.title ASC
    LIMIT 10
    I am running Ubuntu Linux 9.10 Intel Xeon 2.00GHz, 4 cores
    MySQL version 5.1.37

    when I go into phpMyadmin and show the processes I see "Copying to tmp table on disk" It takes about 2 minutes to run the query

    Some of the tables have about 300,000 record in it.

    here is what my my.cnf looks like

    Code:
    [client]
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    
    [mysqld_safe]
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    
    [mysqld]
    
    user		= mysql
    pid-file	= /var/run/mysqld/mysqld.pid
    socket		= /var/run/mysqld/mysqld.sock
    port		= 3306
    basedir		= /usr
    datadir		= /var/lib/mysql
    tmpdir		= /tmp/
    #skip-external-locking
    #skip-networking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    #bind-address		= 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer		= 16M
    max_allowed_packet	= 16M
    thread_stack		= 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    table_cache            = 1024
    #thread_concurrency     = 10
    # * Query Cache Configuration
    query_cache_limit	= 1M
    query_cache_size        = 16M
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
    # Here you can see queries with especially long duration
    #log_slow_queries	= /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    #server-id		= 1
    #log_bin			= /var/log/mysql/mysql-bin.log
    expire_logs_days	= 10
    max_binlog_size         = 100M
    #binlog_do_db		= include_database_name
    #binlog_ignore_db	= include_database_name
    # * InnoDB
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    [mysqldump]
    quick
    quote-names
    max_allowed_packet	= 16M
    [mysql]
    [isamchk]
    key_buffer		= 16M
    !includedir /etc/mysql/conf.d/
    what should I change to get this to run faster The Storage Engine is InnoDB

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    Put the word EXPLAIN in front of that query and run that. Show us what EXPLAIN shows you.

    Why are you using GROUP BY??? There is no aggregat function in use.

    Did you instead mean to do SELECT DISTINCT?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Jan 2009
    Posts
    34
    Thanks
    1
    Thanked 0 Times in 0 Posts

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    That looks like it's as good as it's going to get.

    The reason MySQL ignores the key for the first table is because your query is, basically, saying "get me all Track records". The LIMIT on the end can't take place until indeed all tracks have been gotten.

    MySQL isn't overly bright when it comes to using indexes.

    Hmmm...what you *MIGHT* want to do is rearrange the query: The LEFT JOINs aren't going to affect which Track records you will get, but the INNER JOIN to Album will.

    So maybe if you *first* created/selected a temp table of TRACKS INNER JOIN ALBUM LIMIT 10 and then LEFT JOINed *it* to the other tables things would get better.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    OH! You are doing ORDER BY Tracks.Title, but you don't have an index on TITLE, so *of course* it has to load in all records!!!

    Try adding an index on Tracks.Title???
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Jan 2009
    Posts
    34
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry am I adding an index to my query or to my table? What do i need to do?

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    To your table.
    Code:
    USE [your database name];
    
    CREATE INDEX ix_tracks_title ON tracks(title);
    If tracks.title should be a unique value (that is, if titles should never be duplicated) just use UNIQUE keyword:
    Code:
    USE [your database name];
    
    CREATE UNIQUE INDEX ix_tracks_title ON tracks(title);
    ("ix_tracks_title" is an arbitrary name, but I think that naming an index with a standard prefix, such as ix, and then the table name and then the field name is a good practice. But up to you.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Jan 2009
    Posts
    34
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have an id field that is my index? why would I make my title field an index I don't won't that field to be unique.

  • #9
    New Coder
    Join Date
    Jan 2009
    Posts
    34
    Thanks
    1
    Thanked 0 Times in 0 Posts
    just an FYI when I add something like AND Genres.Name LIKE 'R&B' it runs really fast

  • #10
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by soundgod1818 View Post
    I have an id field that is my index? why would I make my title field an index I don't won't that field to be unique.
    making an index on the title field can save a table scan and make your query run quicker. Nothing to do with records having to be unique.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    Only if you use the UNIQUE keyword when you create the index will it require unique values. So don't use it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New Coder
    Join Date
    Jan 2009
    Posts
    34
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I guess i need more help

    the name of my database is etracks
    you want me to make the title row in the Tracks table an ID? how do i do that I am using phpMyadmin

    here is my updated mysql query
    Code:
    SELECT
    		Tracks.id, 
    		Album.idAlbum AS idAlbum, 
    		Track_Artist.idTrack_Artist AS idTrack_Artist, 
    		Tracks.file_location_hash, 
    		Tracks.file_path, 
    		Tracks.file_name, 
    		Tracks.sample_rate, 
    		Tracks.`year`, 
    		Tracks.title,
    		Tracks.track_number,
    		Tracks.bpm,
    		Tracks.duration, 	
    		Tracks.extension, 	
    		Tracks.scan_date, 
    		Genres.Name as genres_name, 
    		Artist.Name as artist_name, 
    		Album.Name AS album_name
    		FROM
    		Tracks
    		Left Outer Join Album ON Album.idAlbum = Tracks.album_id
    		Left Outer Join Track_Artist ON Track_Artist.idTrack = Tracks.id
    		Left Outer Join Artist ON Artist.idArtist = Track_Artist.idArtist
    		Left Outer Join Track_Performer ON Track_Performer.idTrack = Tracks.id
    		Left Outer Join People ON People.idPeople = Track_Performer.idPeople
    		Left Outer Join Performer_Function ON Performer_Function.idPerformer_Function = Track_Performer.idfunction
    		Left Outer Join Track_Genres ON Track_Genres.idTrack = Tracks.id
    		Inner Join Genres ON Genres.idGenres = Track_Genres.idGenres
    		Left Outer Join Artist_People ON Artist_People.idArtist = Artist.idArtist AND Artist_People.idPeople = People.idPeople
    this is really fast but if ai add an order by line to that like
    ORDER BY Artist.Name ASC it will really slow it down I am trying to run EXPLAIN but that is taking a long time

    what am i missing?

  • #13
    New Coder
    Join Date
    Jan 2009
    Posts
    34
    Thanks
    1
    Thanked 0 Times in 0 Posts

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    *ANY* field that you want to ORDER BY should be indexed. Period.

    We could also rewrite the query to improve performance *IF* the ORDER BY field is in the TRACKS or GENRES tables. (Can't help much if the ORDER BY comes from one of the LEFT JOIN'ed tables. But at least putting an index on the field even then will help.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #15
    New Coder
    Join Date
    Jan 2009
    Posts
    34
    Thanks
    1
    Thanked 0 Times in 0 Posts
    so i would run ALTER TABLE `Album` ADD INDEX ( `Name` ) and change the table and row I am going to order by?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •