What is the issue with this query ? It is taking 0.5 to 0.6 seconds to load data from mysql table with more than 220,000 records

Question

SELECT correct 
FROM  `answers` 
WHERE  `assignment_id` =2055
AND (
`correct` =  'N' ||  'NA'
)
AND  `topic_id` =  '50#j1_5'
AND  `student_id` 
IN ( 702, 223, 237, 252, 229, 246, 1050, 256, 248, 1049, 243, 241, 244, 242, 249, 236, 250, 245 ) 
LIMIT 0 , 30

Any helping hands how to get data faster ?See the explain result

its taking less than a second to get 220,000 results and youre complaining about getting it faster? to me, thats already pretty fast — mast3rd3mon, Apr 30 '18 at 11:02
i understood the table has 220,000, not the result... what indices do you have defined? — Peter Szoldan, Apr 30 '18 at 11:03
How to know if there is any index given to the table ? See the link of image attached in the post where it explains the query — Tetalks, Apr 30 '18 at 11:07
Your query has no indexes to use. Indexes make a MASSIVE difference to performance (imagine the difference between trying to find someone in a paper phone book by their name, as opposed to trying to find it by their address). Post the table declare. — Kickstart, Apr 30 '18 at 11:16
I can see no indexing is there , how to provide the valid index for the table? See the explain data for the query above — Tetalks, Apr 30 '18 at 11:16
@Kickstart : how to add indexing to this table ? Help me out here? — Tetalks, Apr 30 '18 at 11:17
https://stackoverflow.com/questions/3002605/how-do-i-add-indices-to-mysql-tables?answertab=active#tab-top — Vipul Solanki, Apr 30 '18 at 11:24

score 0 · Accepted Answer · answered Apr 30 '18 at 11:23

0

This isn't a full answer (at least for now), but too big to be a comment.

You can add an index to a table. Syntax to do so is here:-

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

I would guess that an index on the columns assignment_id and student_id would probably help most.

ALTER TABLE `answers` ADD INDEX `assignment_id_student_id` (`assignment_id`, `student_id`);

This might take some time (tbh, probably not in this situation, but something to be aware of with larger / more complex indices).

Note that MySQL in general will use a single index on a table, and not combine indices. Hence you set up an index that covers more than one column.

answered Apr 30 '18 at 11:23

Kickstart

21,403
2
21
33

what about the topic column ? Should that also be added or not ? Will adding this index cause some issue to already existing data ? – Tetalks Apr 30 '18 at 11:25
Possible. But that is not a numeric column (so the index will be rather larger) and the benefits might be minimal. How many topics are there for each assignment_id / student_id? – Kickstart Apr 30 '18 at 11:28
there are about 2000 topics , so 2000 different topics can come in topic field , and topic column data eg : 23#asjci – Tetalks Apr 30 '18 at 11:30
But how much is that cut down by the number of assignments? How many topics per assignment? – Kickstart Apr 30 '18 at 11:32
That is , For eg : there are 10 questions each 10 question will have 10 topics connected to it , and this 10 questions are combined as one assessment and is shared to a group of 100 students , so , when each student answers an assessment 10 rows will be added in answer table , so total 10 questions * 100 students = 1000 rows – Tetalks Apr 30 '18 at 11:36
In that case it might be worth adding the topic id field to the index. – Kickstart Apr 30 '18 at 11:41
Can you give me a query after adding topic also? – Tetalks Apr 30 '18 at 11:59
ALTER TABLE `answers` ADD INDEX `assignment_student_topic` (`assignment_id`, `student_id`, `topic_id`); – Kickstart Apr 30 '18 at 12:01

score 0 · Answer 2 · answered May 10 '18 at 04:17

correct = 'N' ||  'NA'

What is that supposed to mean?

correct = CONCAT('N', 'NA')
correct = 'N' OR correct = 'NA'

Well, it does not mean either of those.

To run faster, add this composite index:

INDEX(assignment_id, topic_id, correct, student_id)

The order of the columns is important. (But this is not the only optimal ordering.) Indexes on single columns is not as good. Neither of Kichstart's indexes is not as good, since it does not include all the = columns first.

Cardinality does not matter.

Numeric versus string -- does not matter in indexes.

What is the issue with this query ? It is taking 0.5 to 0.6 seconds to load data from mysql table with more than 220,000 records

2 Answers2