I am using the following code for calculating ranking and it is working great for not large data. But when it comes to large data, processing time takes almost a minute. Please can anyone suggest a better way or atleast a faster way to put the same mysql statement? SELECT id, Names, TOTALSCORE, Rank FROM (   SELECT t.*,      IF(@p = TOTALSCORE, @n, @n := @n + 1) AS Rank,      @p := TOTALSCORE  FROM(  SELECT id, Names,SUM(score) TOTALSCORE     FROM exam e1, (SELECT @n := 0, @p := 0) n     WHERE NOT EXISTS(        SELECT null FROM exam e2        WHERE e1.id = e2.id        AND e2.score/e2.Fullmark < 0.33      )     GROUP BY id     ORDER BY TOTALSCORE DESC  ) t ) r ;Fiddle is here:ranking with ties and skipping rows on passmark condition
Performance problem: Ranking with ties and skipping rows on passmark condition
performance;mysql;sql
First, let's confirm the intention of your query.  According to my interpretation, it ranks students according to the sum of their exam scores, best students first, but disqualifying anyone who has ever failed an exam by scoring lower than 33%.  (Why does the disqualification rule exist?  Does failing an exam allow a student to take it again?  If so, a fairer rule would be to disqualify students who have retaken an exam.)Next, I would like to point out problems in your schema.  You included this table definition in your SQL Fiddle, which you should also have stated within the question itself (it's essential information for reviewing your code):CREATE TABLE exam    (`id` int, `Names` varchar(6), `Subject` varchar(8), `Exam` varchar(5), `Fullmark` int, `score` int);Naming the first column id is weird and tricky: at first glance, one would expect the id column of an exam table to identify an exam, but you use it as a student ID.That oddity is an indication of another anomaly: your schema is denormalized.  There is a M:N relationship between students and exam scores.  (Each student takes M exams; each exam is taken by N students.)  There should be three tables:CREATE TABLE student( id SERIAL PRIMARY KEY, `Names` VARCHAR(6) NOT NULL  -- Probably too short);-- Using the long name to distinguish it from your exam tableCREATE TABLE examination( id SERIAL PRIMARY KEY, `Subject` VARCHAR(8) NOT NULL, `Type` VARCHAR(5) NOT NULL, `Fullmark` INTEGER NOT NULL);CREATE TABLE examination_score( student_id INTEGER NOT NULL, examination_id INTEGER NOT NULL, `Score` INTEGER NOT NULL, FOREIGN KEY (student_id) REFERENCES student (id), FOREIGN KEY (examination_id) REFERENCES examination (id));To populate the new tables, you would use the queryINSERT INTO student    SELECT DISTINCT `id`, `Names`        FROM exam;INSERT INTO examination (`Subject`, `Type`, `Fullmark`)    SELECT DISTINCT `Subject`, `Exam`, `Fullmark`        FROM exam        ORDER BY `Subject`, `Exam`;INSERT INTO examination_score    SELECT exam.id AS student_id         , examination.id AS examination_id         , exam.score AS score        FROM exam            INNER JOIN examination                ON examination.`Subject` = exam.`Subject`                AND examination.`Type` = exam.`Exam`                AND examination.`Fullmark` = exam.`Fullmark`;Notice that you misspelled Science in two different ways, as well as Mizo (whatever that is).  The PRIMARY KEY constraint caught the misspelling of the student names Lawma and Thanga.Verify that this query works:SELECT student_id     , SUM(`Score`) AS TOTALSCORE    FROM examination_score AS e        WHERE NOT EXISTS (            SELECT dq.student_id                FROM examination_score AS dq                    INNER JOIN examination                        ON examination.id = dq.examination_id                WHERE                    dq.`Score` / `Fullmark` < 0.33                    AND dq.student_id = e.student_id        )    GROUP BY student_id    ORDER BY TOTALSCORE DESC;Compare its performance against the equivalent query based on the denormalized schema:SELECT id, Names,SUM(score) TOTALSCORE   FROM exam e1   WHERE NOT EXISTS(      SELECT null FROM exam e2      WHERE e1.id = e2.id      AND e2.score/e2.Fullmark < 0.33    )   GROUP BY id   ORDER BY TOTALSCORE DESC;My guess is that the revised query will be faster.  The root cause is that you didn't have an index on the id column of your exam table.  You could just create the index, but normalizing the schema and writing the appropriate PRIMARY KEY constraints would implicitly create such indexes, and is better practice.