My website is built using Django and MySQL. The content table in the MySQL database contains nearly 20 fields, including id, title, description, content, analyze, and so on. Some of these fields are of varchar type, some are of text type, and some also store JSON data. There are now over 30000 pieces of data in the content table, with myd files accounting for approximately 33GB.
After conducting some like queries based on the above fields, I found that the search speed is very slow, possibly reaching over 2 minutes. I only indexed the ID in the content table. What should be done with such a MySQL search index to improve search speed? Or is there any other way? I originally wanted to try Elasticsearch, but the server memory is not enough.
I only indexed the ID in the content table.
2
Answers
In order to make your queries fast. You should try database normalisation. Detect the columns which are used to filter table frequently, and apply indexing on them.
Database normalisation and indexing most searched columns will make your queries fast, and also it will not cost much storage
Use database indexing in your django models