Quelques requêtes SQL pour interroger la base

requête pour savoir le nb de clients qui utilisent les permissions utilisateurs :

SELECT
   users.idClient,
   users.login,
   acl.*,
   count(*) as nb
FROM acl,users
WHERE allow=0
   AND acl.idAro=users.idAro -- jointure acl - users
GROUP BY users.idClient

Requête pour avoir le nb de comptes ouverts :

SELECT count(*) as nb
FROM clients
WHERE isvalidated=1
   AND closed=0

Requête pour avoir le compte des options actives :

SELECT  glouxProducts.reference,
   glouxProducts.name,
   COUNT( clientBrickOptions.id )
FROM clients
   INNER JOIN clientBricks ON clients.id = clientBricks.idClient
   INNER JOIN clientBrickOptions ON clientBricks.id = clientBrickOptions.idClientBrick
   INNER JOIN resellerBrickOptions ON clientBrickOptions.idResellerBrickOption = resellerBrickOptions.id
   INNER JOIN resellerAtomicProducts ON resellerBrickOptions.idResellerAtomicProduct = resellerAtomicProducts.id
   INNER JOIN glouxProducts ON resellerAtomicProducts.idGlouxProduct = glouxProducts.id
WHERE clients.closed = '0'
GROUP BY glouxProducts.reference

Requête pour avoir les bases mysql qui dépassent 100 Mo

SELECT mysqlAccounts.*,diskUsage.diskUsage
FROM mysqlAccounts,diskUsage
WHERE diskUsage.diskUsage > 100
	AND diskUsage.when = '2009-08-14'
	AND mysqlAccounts.id = diskUsage.idClientRessource  -- jointure mysqlAccounts - diskUsage
ORDER BY diskUsage DESC

Requête pour avoir toutes les ressources prises par les briques orphelines

SELECT *,count(*) as nb
FROM clientRessources
WHERE idClientBrick in
  ( SELECT clientBricks.id
    FROM clientBricks
    LEFT JOIN clients ON clients.id = clientBricks.idClient
    WHERE clients.id is NULL )
GROUP By idResellerAtomicProduct

Requête pour avoir l'espace disque utilisé par les vhosts fermés avant le 2010-01-01 de client closed. (résultat : 20Go)

select sum(du.diskUsage) as tt
from gloux_prod.clients as c,
  gloux_prod.clientRessources as r,
  panel.diskUsage as du
where c.closed=1
  and r.idClient = c.id
  and r.idResellerAtomicProduct = 21
  and r.status = 'disabled'
  and r.updateDate < '2010-01-01'
  and du.idClientRessource = r.id
  and du.when = '2010-05-30'
group by c.closed

Requête pour avoir l'espace disque utilisé par les ftp main de clients fermés : (résultat 31 Go)

select sum(du.diskUsage) as tt

from gloux_prod.clients as c,
  gloux_prod.clientRessources as r,
  panel.ftpAccounts as ftp,
  panel.diskUsage as du

where c.closed=1
  and r.idClient = c.id
  and r.idResellerAtomicProduct = 18
  and r.status = 'disabled'
  and r.updateDate < '2010-01-01'
  and ftp.id = r.id
  and ftp.main = 'Y'
  and du.idClientRessource = r.id
  and du.when = '2010-04-30'

group by c.closed

Espace utilisé par les ftp main des clients, groupés par closed (résultat : closed=0 105Go ; closed=1 46Go)

select c.closed,sum(du.diskUsage) as tt

from gloux_prod.clients as c,
  gloux_prod.clientRessources as r,
  panel.ftpAccounts as ftp,
  panel.diskUsage as du

where r.idClient = c.id
  and r.idResellerAtomicProduct = 18
  and ftp.id = r.id
  and ftp.main = 'Y'
  and du.idClientRessource = r.id
  and du.when = '2010-05-30'

group by c.closed

Répartition des bases mysql entre les clients closed=0 et closed=1 (closed=0 : 2131 bases ; closed=1 : 1057 bases)

SELECT count(*) as nb,c.closed,r.*

FROM mysqlAccounts as my,
  clientRessources as r,
  gloux_prod.clients as c

WHERE r.id = my.id
  and r.idClient = c.id

GROUP BY c.closed