I have a table which have three columns id, copyId, and referral
id is primary key and int(11)
copyId is varchar (255)
referral is medium blob
In referral column i am saving links in josn array
like this
array('http://example1.com/id','http://example2.com/id','http://example3.com/id','http://example4.com/id');
convert above array in json_encode and save into referral column
my all queries search id in referral column links and i have tons of load in database i think my this table take much loads this table have approx 160,000 records and each blob have 500 above links when i have do research i found that index is very good for retrieving data.
I want your help to indexing blob and give me suggestions what should i do for improving preformance of this table and i am using phpmyadmin for indexing please don't tell me query tell me what index should i apply on referral column.
1) index 2) fulltext 3) primary 4) spatial 5) unique
and i have not any use of id column which is primary key i think i should delete this column is it a good idea for better performance.
And please suggest me what should i do for mysql better performance Thank you