dev-ria

thanks guys for the info! how can i use ON DUPLICATE KEY UPDATE? what does it do exactly? because the table (table b ) am inserting into does not have any data so therefore would not have any duplicates. I looked at mysql dev but dont really understand what it's doing exactly.

Hello, So i have query that takes data from table a and inserts into table b (which is empty) and updates field copied_date in table a. so something like this INSERT INTO `b`(`ba`,`bb`,`bc`) SELECT `aa,`,`ab`,`ac` FROM `a`'; UPDATE `a` SET `Copied_date` = 'NOW()'; I want to know how this query actually works and if I can simplify it even more. Does it INSERT INTO first of all the data then goes back and UPDATES? or does it work all in one query kind of way? Thanks

here is sample of what the data looks like

ugh its calling LastCount (added LastCount to first SELECT) but it still counts the same as first FirstCount. (SELECT LastExtracted,FirstExtracted, COUNT(FirstExtracted) AS FirstCount, COUNT(LastExtracted) AS LastCount, DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate, DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate FROM results WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d')) UNION ALL (SELECT LastExtracted,FirstExtracted, COUNT(FirstExtracted) AS FirstCount, COUNT(LastExtracted) AS LastCount, DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate, DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate FROM results WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') GROUP BY Firstdate) ORDER BY Firstdate DESC LIMIT 20

thanks for the help guys. pantu the query didnt work as planned. it counted all the records in the field and didnt split them by date. but the query below i put together seems to work except when I call the LastCount in my while() it comes back as Notice: Undefined index: LastCount SELECT LastExtracted,FirstExtracted, COUNT(FirstExtracted) AS FirstCount, DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate, DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate FROM results WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') UNION ALL SELECT LastExtracted,FirstExtracted, COUNT(LastExtracted) AS LastCount, DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') AS Lastdate, DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') AS Firstdate FROM results WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') GROUP BY Firstdate ORDER BY Firstdate DESC LIMIT 20 the reason for the limit is im creating a graph so i only need the last 20 days.

i ran it on a php page and mysql workbench. they only bring count of FirstExtracted. It makes count of LastExtracted the same as the FirstExtracted.

hello, i need to count two different fields in one query but I keep getting the count of only the first field FirstExtracted. SELECT artist,title,url,FirstExtracted, COUNT(FirstExtracted) AS FirstCount, COUNT(LastExtracted) AS LastCount, DATE_FORMAT(`FirstExtracted`,'%m-%d') AS Seconddate FROM results WHERE DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') = DATE_FORMAT(`LastExtracted`,'%Y-%m-%d') GROUP BY Seconddate ORDER BY DATE_FORMAT(`FirstExtracted`,'%Y-%m-%d') DESC LIMIT 20 I need results as $run['Seconddate']."', ".$run['FirstCount'].",".$run['LastCount'] not sure what I'm missing here.

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.

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.

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

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?

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

this is what i get in phpmyadmin

$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

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?