Mysql sum function using on the large table making slow in response

Databases
1

i have table that contain 23 colulmns and 31000 records

table structure is as follows
CREATE TABLE fee (
id int(11) NOT NULL auto_increment,
studentid varchar(10) NOT NULL,
month int(15) NOT NULL,
year int(4) NOT NULL,
feedate date NOT NULL,
amount int(6) NOT NULL,
total int(5) NOT NULL,
amtrec int(5) NOT NULL,
annual int(5) NOT NULL,
others int(11) NOT NULL default ‘0’,
paymenttype varchar(15) NOT NULL,
books int(5) NOT NULL,
uniform int(5) NOT NULL,
checkno int(6) NOT NULL,
checkclear varchar(10) NOT NULL,
paytype varchar(15) NOT NULL,
fine int(5) NOT NULL,
feedue int(5) NOT NULL,
annualrec int(11) NOT NULL,
adrecd int(11) NOT NULL,
discount int(11) NOT NULL,
srno int(11) NOT NULL,
DRAWN varchar(30) NOT NULL,
PRIMARY KEY (id)
)

I have to write a query to get the sum of total column where studentid is xyz

so i m writing SELECT sum(total) WHERE studentid=‘xyz’ GROUP BY studentid

but it is taking 2 minutes to show the result.

what could be the problem any help is appreciated

2

Yup, MyISAM and InnoDB tables only support BTREE indexes, so that’s the one MySQL will choose by itself :slight_smile:

3

You means i have run this
CREATE INDEX studentnr_ix (studentid(10))

It itself decide that it have to perform a btree index ? and apply it by it self .

4

It has to create a BTREE index for 31000 rows, that could take a while :slight_smile:

5

I have done lets see what happen as my server is not responding

6

then why did this index not show when you posted the CREATE TABLE statement?

7

Sir but i cant as studentid is a code that is logically made and now at this moment i can not change, second thing is i have to sum the ‘total’ column
not the studentid

… please help

8

Thanks For the help actually our database server got stuck up and have some problem so lets see tommorow what happen but i m really thank ful to u for helping me well if the problem still persist i will ping u back tommorow

Thanks

9

choose student id as digit rather than text like ‘xyz’…
Query will fetch the string match some what slow when compare to digit or number…

10

i have a table which is as follows
CREATE TABLE fee (
id int(11) NOT NULL auto_increment,
studentid varchar(10) NOT NULL,
month int(15) NOT NULL,
year int(4) NOT NULL,
feedate date NOT NULL,
amount int(6) NOT NULL,
total int(5) NOT NULL,
amtrec int(5) NOT NULL,
annual int(5) NOT NULL,
others int(11) NOT NULL default ‘0’,
paymenttype varchar(15) NOT NULL,
books int(5) NOT NULL,
uniform int(5) NOT NULL,
checkno int(6) NOT NULL,
checkclear varchar(10) NOT NULL,
paytype varchar(15) NOT NULL,
fine int(5) NOT NULL,
feedue int(5) NOT NULL,
annualrec int(11) NOT NULL,
adrecd int(11) NOT NULL,
discount int(11) NOT NULL,
srno int(11) NOT NULL,
DRAWN varchar(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=89 ;

i am writing a query as
SELECT SUM(total) FROM fee WHERE studentid=‘xyz’ GROUP BY studentid
It takes 2 min or more to response

the table have 31000 records

11

CREATE INDEX studentnr_ix (studentidB[/B])

As can be found in da manual: http://dev.mysql.com/doc/refman/5.0/en/create-index.html

12

How to define the length of a index ? please tell ?

13

Why do yoy have to indexes studentid and studentid_2? On what fields are these indexes defined?

Also, why is key_len 12 for a field that is varchar(10) ?
I would drop the index, create it anew with a shorter length (max 10, but shorter may also work, dependent on the distribution of student_ids) and see if that helps.

14 

id 	select_type 	table 	type 	possible_keys 	        key 	    key_len 	ref 	
1 	SIMPLE 	        fee    ref 	studentid,studentid_2 	studentid 	12 	const 	1 	Using where
15

Have you tried an EXPLAIN SELECT sum(total) WHERE studentid=‘xyz’ GROUP BY studentid ?
Does that give you any insights? i.e., is MySQL actually using the index you created?

16

i had already add an index on that field :frowning:

17 
ALTER TABLE fee ADD INDEX studentid_ix ( studentid )

then try your query again

18

SELECT SUM(total) FROM foo_table WHERE studentid=‘xyz098’

like this .

19

i have to sum the ‘total’ column
not the studentid

what do you mean?

Is “select count(*) from foo_table” enough?