Query Takes Forever To Run!

[dev-ria]

hello,

i am running the following query and takes more than 80 seconds to load.

 

 

$query = mysql_query("

SELECT artist,title,url,

COUNT(title) AS TitleCount

FROM results

GROUP BY artist

ORDER BY TitleCount DESC

LIMIT 20",$link2);

 

 

$query2 = mysql_query("

SELECT artist,title,url

FROM results

GROUP BY artist

LIMIT 88",$link2);

 

both run separate while statements and there are about 3.5 million data in the mysql db. no indexes, yet. what can i do to speed this up or clean up my query?

 

thanks

[Barand]

no indexes

 

You may have identified the problem there

[dev-ria]

i am considering indexes but i have yet to determine if it will help more or hurt more. My system is constantly using UPDATE and INSERT. by constantly i mean it is running 24/7 every hour. How much will indexing effect UPDATE and INSERT in mysql?

[ManiacDan]

24/7...every hour?

 

How many inserts per second are you doing? If the answer is less than 10, you can enable proper indexes. Your database is relatively small.

[dev-ria]

it is less than 10 per second. what are your recommendations on setting up indexes? i have about 4-5 most commonly used fields artist, title, url, date1, date2 etc. could i index all of these? should i do anything specific prior to indexing?

[ManiacDan]

Your queries filter on artist and title, that's all. The items in the WHERE are what matter.

[dev-ria]

true. I think I will start with only those two to begin with. Do you have any recommendations on what I should look for after indexing?

[dev-ria]

ok ive indexed artist, title but query still takes as long. what am i doing wrong here?

[ManiacDan]

Run an EXPLAIN on the query and see if it's using the right indexes.

[dev-ria]

how do i go about using the indexes? I named them like so artist_index title_index and i get this when I use EXPLAIN

 

Notice: Undefined index: artist

 

should i change the fields in my query from artist to artist_index?

[dev-ria]

i have feeling im not doing this right. It seems to be taking the same amount of time as before indexing. do i need to specify indexes in each query?

[mikosiko]

post your exact EXPLAIN output (what you posted before is far from that)

[dev-ria]

$query = mysql_query("

EXPLAIN SELECT artist,title,url,

COUNT(title) AS TitleCount

FROM results

GROUP BY artist

ORDER BY TitleCount DESC

LIMIT 20",$link2) or die(mysql_error());

 

gives

 

Edited October 22, 2012 by dev-ria

[kicken]

Run the explain query in something like phpMyAdmin or the mysql console utility where it will just dump the result set for you.

[dev-ria]

this is what i get in phpmyadmin

[mikosiko]

try creating a composite index on (artist, tittle) and run the EXPLAIN again to see if that helps a little

[dev-ria]

im currently doing the composite index. should i delete the separate index for artist title?

[dev-ria]

using composite index it takes about 10-12 seconds to run both queries. is it safe to drop the artist and title index?

 

and keep just the composite index?

Edited October 22, 2012 by dev-ria

[mikosiko]

yes... and when post your explain results post the execution time also.

 

I will suggest also to post more of the relevant code around those queries, maybe something else (in addition to the aggregate function) is contributing to the slowliness

[dev-ria]

thanks!

so basically these two queries are bringing up results to put into google graphs

 

http://code.google.c...on#column_chart

 

using column chart - i display the results in google graphs format. this is the full query that sits in the header of the page in javascript for the google graph

 

$query2 = mysql_query("
SELECT artist,title,label
FROM results
WHERE domain = '".$_GET['domain']."'
GROUP BY artist
LIMIT 200",$link2) or die (mysql_error());

while ($run2 = mysql_fetch_array($query2))
{
echo "['".$run2['artist']."', '".$run2['title']."', '".$run2['label']."'],";
}

//second graph ---------

$query = mysql_query("
SELECT artist,title,url,
COUNT(title) AS TitleCount
FROM results
WHERE domain = '".$_GET['domain']."'
GROUP BY artist
ORDER BY TitleCount DESC
LIMIT 20",$link2);.


while($run = mysql_fetch_array($query))
{
echo "['".$run['artist']."', ".$run['TitleCount']."],";
}

 

so far indexing has helped but im still looking to cut the time to about 3-5 seconds

 

 

 

here is EXPLAIN of the two queries

 

1, SIMPLE, results, ref, domain_index, domain_index, 258, const, 1, Using where; Using temporary; Using filesort

 

next one

 

1, SIMPLE, results, ref, domain_index, domain_index, 258, const, 1, Using where; Using temporary; Using filesort

[mikosiko]

interestingly enough seems that you "forgot" to mention in every single previous post that your query was using the "domain" column too in the where clause, neither you used that column in the previous EXPLAINS, therefore the EXPLAIN that you are posting now is completely different than before...

 

take out the GROUP BY in the query2... maybe you intent was to use ORDER BY instead... good luck

[ManiacDan]

You're kidding. You must be kidding. You've been asking us to fix a FAKE query all day?

[dev-ria]

yea i realized that i need to use the domain column too. but i have indexed that as well so it should speed things up. so far composite index are artist and title and regular index is domain. i can't see why this shouldn't help speed things up. when I remove the where clause the EXPLAIN key is the artist_title_index.

[dev-ria]

no its a not fake query. I've been using this query the whole time. i am using artist and title then i am using domain as well. the WHERE for domain comes later on so I was testing to see how it would act.

[DavidAM]

SELECT artist,title,url,COUNT(title) AS TitleCount
FROM results 
GROUP BY artist
ORDER BY TitleCount DESC
LIMIT 20


SELECT artist,title,url
FROM results 
GROUP BY artist
LIMIT 88
[/Code]


 
Since there is no WHERE clause, both of those queries need to read the [i]entire[/i] table. A composite index on artist, title, and url; [i]might[/i] prevent a table scan. I have worked with some database servers that will realize that the query is "covered" by the index, and scan only the index. I'm not sure if mySql will do that or not.
 
[code]
SELECT artist,title,label
FROM results
WHERE domain = '".$_GET['domain']."'
GROUP BY artist
LIMIT 200

 

Any given query will use only one index per table. So an index on domain will limit the number of rows to be read. It is not likely that any indexes that do not have domain as the first component will help in this situation.

 

Your queries filter on artist and title, that's all. The items in the WHERE are what matter.

 

Just FYI. Fields used in the ON clause of a JOIN matter as well. Since those are usually FOREIGN KEYS, they are usually indexed anyway; but it bears mentioning here.