Performance problem: Ranking with ties and skipping rows on passmark condition 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 &lt; 0.33
      )
     GROUP BY id
     ORDER BY TOTALSCORE DESC
  ) t
 ) r
 ;


Fiddle is here:ranking with ties and skipping rows on passmark condition
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 table
CREATE 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 query

INSERT 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` &lt; 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 &lt; 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.