Sample Header Ad - 728x90

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