En regardant le diverses réponses pour ORDER BY avec CASE comme celle-ci, je vois que ce que je suis obligé de faire dans cette application héritée est probablement une méthode experte; cependant, il est trop lent lorsque les lignes sont moins que triviales (les lignes de 100 000 ou plus entraînent des chargements de page de 10 secondes).

Veuillez noter que la requête d'origine cherche à résoudre un problème apparemment courant où l'analyste de requêtes a besoin de dates vides triées par rapport à la façon dont elles seraient normalement triées. Dans ce cas, datefirstprinted doit être décroissant, mais tous les enregistrements qui ne sont pas imprimés doivent être remplis en haut de la liste.

La Requête d'origine résout ce problème, mais le but de la question est d'éviter le filesort impact sur les performances fourni avec la colonne dérivée notprintedyet.

Requête d'origine

SELECT SQL_NO_CACHE
  id, daterun, datefirstprinted,
  case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end as notprintedyet
FROM
  patientrecords
WHERE
  dateuploaded <> '0000-00-00 00:00:00'
ORDER BY
  notprintedyet desc,                                 /* ordered via alias */
  datefirstprinted desc
LIMIT 10;

heure 1,52 s


J'ai trouvé que ne pas trier sur l'alias notprintedyet économise un peu:

Requête légèrement plus rapide

SELECT SQL_NO_CACHE
  id, daterun, datefirstprinted,
  case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end as notprintedyet
FROM
  patientrecords
WHERE
  dateuploaded <> '0000-00-00 00:00:00'
ORDER BY
  datefirstprinted = "0000-00-00 00:00:00" desc,      /* directly ordered */
  datefirstprinted
LIMIT 10;

temps 1,37 s


Vitesse optimale, mais il manque d'abord le tri requis des dates vides

SELECT SQL_NO_CACHE
  id, daterun, datefirstprinted,
  case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end as notprintedyet
FROM
  patientrecords
WHERE
  dateuploaded <> '0000-00-00 00:00:00'
ORDER BY                        
  datefirstprinted                                     /* not ordered properly */
LIMIT 10;

temps 0,48 s


J'ai essayé d'utiliser une vue

create view notprinted_patientrecords as (
   SELECT id, daterun, datefirstprinted, case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end notprintedyet
   FROM patientrecords
   WHERE dateuploaded <> '0000-00-00 00:00:00'
);

malheureusement, quand je lance expliquez

 explain select * from notprinted_patientrecords order by notprintedyet desc limit 10;

cela montre que j'utilise toujours filesort et prend 1,51 s c'est-à-dire pas d'économies du tout


Serait-ce plus rapide si la valeur par défaut de datefirstprinted est NULL?

peut-être, mais dans cette ancienne application, cela pourrait faire plus de mal que les 5 secondes supplémentaires du temps de chargement de la page


Que pouvons-nous essayer d'autre? Procédures stockées? Les fonctions?


MISES À JOUR

Comme suggéré @strawberry - COMMANDE PAR CAS

...
ORDER BY                        
  case datefirstprinted when "0000-00-00 00:00:00" then 1 else 0 end, datefirstprinted
LIMIT 10;

heure 1,52 s


Comme demandé par @ e4c5, la sortie explain:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: patientrecords
         type: range
possible_keys: dateuploaded,uploads_report
          key: dateuploaded
      key_len: 5
          ref: NULL
         rows: 299095
        Extra: Using index condition; Using filesort

sauf pour pas commandé correctement qui présente la variance suivante

        rows: 10
        Extra: Using where

instruction de création de table

*************************** 1. row ***************************
Table: patientrecords
Create Table: CREATE TABLE `patientrecords` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `datecreated` datetime NOT NULL,
  `dateuploaded` datetime NOT NULL,
  `daterun` datetime NOT NULL,
  `datebilled` datetime NOT NULL,
  `datefirstprinted` datetime NOT NULL,
  `datelastprinted` datetime NOT NULL,
  `client` varchar(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `dateuploaded` (`dateuploaded`),
  KEY `daterun` (`daterun`),
  KEY `uploads_report` (`dateuploaded`,`client`),
  KEY `datefirstprinted` (`datefirstprinted`),
  KEY `datelastprinted` (`datelastprinted`)
)
1
WEBjuju 17 janv. 2017 à 06:54

2 réponses

Meilleure réponse

Suite aux idées apprises sur les index concaténés grâce à @ e4c5, j'ai essayé d'ajouter une clé sur les deux colonnes (colonne utilisée dans where et colonne utilisée dans la clause case basée order):

alter table
  patientrecords
add index
  printedvsuploaded (datefirstprinted, dateuploaded);

Cela n'avait initialement aucun effet puisque mysql continuait à utiliser l'index dateuploaded.

Cependant, l'ajout de force index réduit le temps de requête:

SELECT SQL_NO_CACHE
  id, daterun, datefirstprinted
FROM
  patientrecords
FORCE INDEX (printedvsuploaded)
WHERE
  dateuploaded <> '0000-00-00 00:00:00'
ORDER BY
  case when datefirstprinted = "0000-00-00 00:00:00" then 1 else 0 end desc,
  datefirstprinted
LIMIT 10;

durée 0,64 seconde

il convient de noter que je suis d'accord avec @ e4c5 pour dire que l'index supplémentaire entraînera éventuellement une baisse des performances des écritures; Je compte sur le développement d'autres feuilles de route pour aider à réduire le nombre d'index. pour le moment, la mise en œuvre de ceci réduira les 10 secondes de chargement de page des ensembles de résultats plus grands à la plage gérable de 3 secondes et est alors la solution qui sera mise en œuvre.

1
WEBjuju 17 janv. 2017 à 13:01

En regardant votre tableau, la première chose à noter est que l'index suivant est redondant

KEY `dateuploaded` (`dateuploaded`),

Son rôle peut être rempli par celui-ci

KEY `uploads_report` (`dateuploaded`,`client`),

Alors abandonnons la clé dateuploaded. Il n'est pas clair si vous utilisez réellement la colonne client dans les requêtes. Si vous ne le faites pas, je pense que changer votre index comme suit vous permettra d'accélérer considérablement

KEY `uploads_report` (`dateuploaded`,`datefirstprinted`,`client`),

C'est parce que mysql ne peut utiliser qu'un seul index par table. Étant donné que l'index de la colonne dateuploaded est utilisé dans la clause where, l'index de datefirstprinted ne peut pas être utilisé. Mais si vous combinez les deux colonnes dans le même index, il peut être utilisé à la fois dans le tri et le where.

Après avoir créé l'index ci-dessus, celui-ci pourrait probablement être supprimé:

KEY `datefirstprinted` (`datefirstprinted`),

Avoir moins d'index accélérera vos insertions et vos mises à jour.

1
e4c5 17 janv. 2017 à 06:02