3

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

Community
  • 1
  • 1
Sufyan
  • 456
  • 2
  • 6
  • 16
  • What MySQL version are you using? Because MySQL MySQL 5.7.8+ has native JSON datatype support. Most likly to want to normalize this table for optimization and querying. If you dont want to normalize this table your best bet is an FULLTEXT index but you still will have an problem with BLOB datatype with internal temporary table handing in MYSQL because MYSQL needs to create an disk based temporary table what will slow down the server read this https://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html – Raymond Nijland Sep 23 '15 at 08:13
  • do you store more than one link in the referral for some reason? I'd have multiple copyId rows in the table, one for each url in the array. But indexes depends on what query you will run on this table. You search copyId and retrieve the referrall? You search an url inside a referral and want the copyId value? – Lelio Faieta Sep 23 '15 at 08:15
  • yes i search url id in referrel url to get copyId and referrel urls – Sufyan Sep 23 '15 at 09:20
  • 1
    Possible duplicate of [Storing large JSON - binary or text](http://stackoverflow.com/questions/41198808/storing-large-json-binary-or-text) – e4c5 Jan 06 '17 at 23:41

0 Answers0