[like] query is very slow. Can't you use index?

[africaadonis]

[like] query is very slow. Can't you use index?

Hello! I am a Chinese user. My customer will search according to the pinyin code. There are 35000 pieces of data in the table to be queried. I want to query [like] according to one of the fields. For example, the pinyin code of amoxicillin capsule is amxljn, and the customer will input a, am and amx in turn to push. Then the fuzzy query condition is a%, am and amx, but the query speed is very slow, customer K. Can't [like] use the index set through [keypath]? If [like] doesn't use indexes, is there any way to speed up fuzzy queries?

var tblSysDiagnosis = { name: 'SysDiagnosis', columns: { // Here "Id" is name of column diagnosisid: {primaryKey: true, dataType: 'number', autoIncrement: true}, diagnosisname: {notNull: true, dataType: 'string'}, diagnosisspellno: {notNull: true, dataType: 'string'}, icdten: {notNull: true, dataType: 'string'}, sicdten: {Null: true, dataType: 'string'}, ischinese: {notNull: true, dataType: 'number'}, isvalid: {notNull: true, dataType: 'number'}, orgcode: {notNull: true, dataType: 'string'}, idxdiagnosisspellno: {keyPath: ['diagnosisspellno']}, idxdiagnosisname: {keyPath: ['diagnosisname']} }, version: 4 }

var results = this.idbconnection.select({ from: 'SysDiagnosis', // skip: 0, limit: 10, ignoreCase: true, where: { diagnosisspellno: { like: self.dataForm.diagnosisspellno + '%' } }, order: { by: 'diagnosisid', type: 'asc' //supprted sort type is - asc,desc } }

[africaadonis]

I just tested the way of regular expression, which is also very slow. I reduced the amount of data to 5000, and the query speed is acceptable.

let self = this // console.log(self.dataForm.diagnosisspellno.toUpperCase()) var sRegExp = new RegExp('^' + (self.dataForm.diagnosisspellno.toUpperCase())) console.log(sRegExp) var results = this.idbconnection.select({ from: 'SysDiagnosis', // skip: 0, limit: 10, ignoreCase: true, where: { diagnosisspellno: { // like: self.dataForm.diagnosisspellno + '%' // in: [self.dataForm.diagnosisspellno] regex: sRegExp } }, order: { by: 'diagnosisid', type: 'asc' //supprted sort type is - asc,desc } }).then(function (results) { //results will be array of objects. console.log(results) self.dataList = results }).catch(function (error) { alert(error.message) })

[ujjwalguptaofficial]

Indexing does not work on Like & regex query . They are like looping through all available rows and checking if condition matches & thus its slower.

35k is not a big amount of data but for client side it might be something big considering all set of devices from mobile, tab to a pc.

How to solve

1. Identify your data - if you can group your data, so that you can use indexing on a column & then query will become faster. The key point here is to use query first which can utilize indexing & after regex query.

e.g -

select({
    from: 'SysDiagnosis',
    // skip: 0,
    limit: 10,
    ignoreCase: true,
    where: {
        name: "my_name",
        diagnosisspellno: {
            regex: sRegExp
        }
    }
})

In the above query we are using regex with '='. We have speciied "=" first & then regex. So "=" will be executed which means it will bring subset of data & after that regex will be executed.

Let's say total data is 1millon and after using name column the subset data is 5k, then regex will be executed on 5k.

2. Every data is unique & you are not able to find any subset

In this case - you gotta store all possible subword of your data like stemming.

e.g - If data is ''ujjwal", then you break it into - 'u', 'uj', 'ujj','ujjw','ujjwa','ujjwal'. and then you dont need to use like query but '='.

So above query will become

select({
    from: 'SysDiagnosis',
    // skip: 0,
    limit: 10,
    ignoreCase: true,
    where: {
        diagnosisspellno:  value
    }
})

[africaadonis]

OK, I'll keep testing. Thank you very much. I'll keep watching the jsstore and recommend it to my friends!