regex - Speedup mysql queries with regexp and order by length -

-

i trying implement search auto-completion feature on website. came far table search terms (cities)

city_id int(10) auto increment, primary city_name varchar(200) index

and query

select * city_names lower(city_name) lower('my_search_term%) order length(city_name) limit 10;

this query returns 10 cities shortest names containing search string, need, it's slow. guess db first searches matches regexp, sorts results length, , picks 10 rows. think better somehow pre-sort data length(city_name), query stops after reaches 10 rows match regexp.

so questions are:

  1. is there way sort (collate?) city_names column content length? i'm not planning alter table data, data need sorted 1 single time.
  2. what db engine , index structure suitable table (city_names data not unique)?
  3. is there way change query in order increase performance?

any ideas welcome.

update: based on zerkms's suggestions, did following:

  1. changed collation latin_general_ci. allowed me rid of lower(city_name) conversion.
  2. created column (sort_index) , populated incremental values ordering length(city_name) , city_name. i.e., short names go first, , "a**" goes before "b**" cities same length. search quicker.
  3. changed default order sort_index ascending. don't need order commands in query.

the table , query like:

sort_index int(10) primary city_name varchar(200) city_id int(10) auto increment  select * city_names city_name lower('my_search_term%) limit 10;

  1. if use *_ci collation string comparisons should case insensitive. city_name lower('term%') should used.
  2. create column , store each city_name column length there
  3. create composite (city_name, city_name_length) index (but there chance city_name_length won't used though)

Comments

Post a Comment

Popular posts from this blog

python - Subclassed QStyledItemDelegate ignores Stylesheet -

-
i'm trying subclass qstyleditemdelegate remove focus rectangle in qcombobox . even though i'm calling base implementation of paint function , nothing else, result different. looks if parts of style-sheet influence bounding box of item taken consideration. class pstyleditemdelegate(qstyleditemdelegate): def __init__(self, *args, **kwds): super(pstyleditemdelegate, self).__init__(*args, **kwds) def paint(self, *args, **kwargs): qstyleditemdelegate.paint(*args, **kwargs) what have paint unmodified qstyleditemdelegate ? as suggested tried replacing pyside pyqt4 , works, appears bug. update pyside 1.1.2 1.2.1 result same. unfortunately switch breaks other parts of code, if there no other suggestions i'm going accept answer. edit bug tracked here
Read more

java - HttpClient 3.1 Connection pooling vs HttpClient 4.3.2 -

-
i have used httpclient3.1(java) connection pooling below : import org.apache.commons.httpclient.httpclient; import org.apache.commons.httpclient.multithreadedhttpconnectionmanager; import org.apache.commons.httpclient.params.httpclientparams; import org.apache.commons.httpclient.params.httpmethodparams; import org.apache.log4j.logger; public class httpclientmanager { private static logger logger = logger.getlogger(httpclientmanager.class); private static multithreadedhttpconnectionmanager connectionmanager = new multithreadedhttpconnectionmanager(); private static httpclientparams clientparams = new httpclientparams(); static { try { clientparams.makelenient(); clientparams.setauthenticationpreemptive(false); clientparams.setparameter(httpmethodparams.so_timeout, new integer(30000)); // set so_timeout clientparams.setparameter(httpmethodparams.head_body_check_timeout, new integer(3000)); // head responses ...
Read more

node.js - StackOverflow API not returning JSON -

-
i using node.js script gather data stackoverflow. know responses gzipped, put code in should take care of that. script follows: var request = require('request'); var zlib = require('zlib'); function getstackoverflowresponse(url, callback){ request(url, {encoding: null}, function(err, response, body){ if(response.headers['content-encoding'] == 'gzip'){ zlib.gunzip(body, function(err, dezipped) { callback(dezipped); }); } else { callback(body); } }); } var url = "https://api.stackexchange.com/docs/questions#pagesize=2&order=desc&min=2014-01-04&max=2014-02-02&sort=activity&tagged=apigee&filter=default&site=stackoverflow&run=true"; getstackoverflowresponse(url, function(questions) { console.log(questions); }); instead of getting json output, i'm getting following response: buffer 0d 0a 0d 0a 0d 0a 0d 0a 3c 21 44 4f 43 54 59 50 45 20 48...
Read more