0

i need some help in some mysql queries i'm trying to improve it's performance and speed
i'm trying to figure out how to do that using mysql syntax .

here is an snippet of a query function i use on a stats php script to fetch some countries info of visitors . 

function countries() {
$countryname = array();
$countrycode = array();
$countrytotal = array();
$countrydownloads = array();
$countryrate = array();
$countries = query ("SELECT country,code, count(*) as total FROM visitors group by country order by total DESC");
 while ($country = mysql_fetch_array($countries))
  {
    $download = query("SELECT COUNT(id) as total FROM visitors WHERE download  = 1 and country = '$country[country]';");
    if(mysql_num_rows($download) == 0) {$downloads[0] = 0;} else { $downloads = mysql_fetch_array($download); }
    $rate = intval( ($downloads[0] * 100) / $country[total])." %";
    array_push($countryname,$country[country]);
    array_push($countrytotal,$country[total]);
    array_push($countrydownloads,$downloads[0]);
    array_push($countryrate,$rate);
    array_push($countrycode,$country[code]);
 }

return array($countryname,$countrytotal,$countrydownloads,$countryrate,$countrycode); }

the query first collect all countries from the table visitors in groups by name . then it collect the downloads for each group resulted from the first query .

my problem that if the visitors table have records of 150 different country grouping them by name then it collect the downloads for each group, if each query of country group will take 0.5 secs the total function will take 150 * 0.5 = 75 secs which is too bad and very slow speed to execute the queries .

so there is 2 problems :

1) i execute a separate query for first function that will return only the country names in groups , then execute another separate query for collecting downloads from each country using the php loop , which takes more time when there are many groups .

2) the second query which counts the downloads for each group , it use to search in the whole visitors table records not only in the group of that country which slow the performance also .

so is there any good solution for these two problems to make only one query that will give the countries in groups by names and the total of them and the downloads from each group and would be faster and giving better performance ?

any help is really appreciated , thanks

Jack
  • 1

1 Answers1

0

SELECT c.country, c.code, COUNT(v.id) AS total FROM country c INNER JOIN visitors v ON (c.country = v.country) WHERE v.download = 1 GROUP BY v.country ORDER BY total DESC;

Your potentially hundreds of query combined into one :). Of course, you have to make sure you have the proper indexes on both tables.

dotmanila
  • 1