Copying to tmp table on disk

[soundgod1818]

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

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

[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

[Old Pedant]

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?

[soundgod1818]

[Old Pedant]

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.

[Old Pedant]

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???

[soundgod1818]

Sorry am I adding an index to my query or to my table? What do i need to do?

[Old Pedant]

To your table.

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:

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.)

[soundgod1818]

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.

[soundgod1818]

just an FYI when I add something like AND Genres.Name LIKE 'R&B' it runs really fast

[bazz]

Originally Posted by soundgod1818

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.

[Old Pedant]

Only if you use the UNIQUE keyword when you create the index will it require unique values. So don't use it.

[soundgod1818]

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

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?

[soundgod1818]

[Old Pedant]

*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.)

[soundgod1818]

so i would run ALTER TABLE `Album` ADD INDEX ( `Name` ) and change the table and row I am going to order by?