Reduce mysql query ( Fulltext-search ) execution runtime, server-side?
0
votes
0
answers
30
views
****VPS**:**
- ubuntu
- Django (latest)
- Python3.6
- Mysql
**Structure :**
CREATE TABLE
tablename
(
id
int(11) NOT NULL AUTO_INCREMENT,
username
varchar(255) CHARACTER SET latin1 NOT NULL,
wallpaper
varchar(200) CHARACTER SET latin1 NOT NULL,
timestamp
datetime(6) NOT NULL,
tags
text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
downloads
int(11) NOT NULL,
wallpaper_preview
varchar(200) CHARACTER SET latin1 NOT NULL,
views
int(11) NOT NULL,
wall_height
int(11) NOT NULL,
wall_size
varchar(10) NOT NULL,
wall_width
int(11) NOT NULL,
PRIMARY KEY (id
),
FULLTEXT KEY tags
(tags
)
) ENGINE=InnoDB AUTO_INCREMENT=14566 DEFAULT CHARSET=utf8
**Code ( from django views.py ):**
posts = mytable.objects.raw('SELECT id,
MATCH (tags) AGAINST (\''+some.tags+'\') as score
FROM table ORDER BY score desc;')
**Excution Time (5 runs in sec, views.py):**
6.103515625e-05
6.4849853515625e-05
6.318092346191406e-05
8.034706115722656e-05
8.273124694824219e-05
**Runtime checked using:**
start = time.time()
posts = modelname.objects.raw('SELECT id,
MATCH (tags) AGAINST (\''+some.tags+'\') as score
FROM table ORDER BY score desc;')
end = time.time()
print(f"Runtime views is {end - start}")
**Tried raw from ssh, mysql command line**
SELECT id, MATCH (tags) AGAINST ('car') as score
FROM tablename ORDER BY score desc;
**result:**
11834 rows in set (0.09 sec)
**Indexed using:**
ALTER TABLE 'tablename' ADD FULLTEXT('tags');
is there a better way. or something with Django ORM.
**example search:**
*'superscars'* searched in *'road,hometown,supercar,extremecars'*
if mysql is performing in 0.09 sec then why django takes so much time
, or i have done something wrong or missed something.
*i have asked this on stackoverflow, they redirected me here*
Asked by Harsh Jadon
(101 rep)
Jul 8, 2021, 09:37 PM
Last activity: Jul 14, 2021, 06:42 PM
Last activity: Jul 14, 2021, 06:42 PM