bharani kumar wrote:
Hi All ,Am working on the autosuggestion program .... SELECT $field2,$field1 FROM $tblname where locate('$q', $field2) > 0 OR locate('$q', $field1) > 0 order by locate('$q', $field2), $field2 limit " . ($pagesize * $page) . "," . $pagesize This is my query , Example .... in my Database table .. am having values somthing like Bombay(BOB),Delhi(DI),Gujarath(GA),Rajasthan(RA),Baroda(BD) My present act like ... If user enter the " d " then It show the baroda(BD) as first row and Delhi as second result .... So can u please guide me here Also for clear vision please go this link http://ukatn.com/index_autosuggest.php Select the Postal code in the Taxi From combo... Then enter the l as keyword . then u will find the first are start with A not an L , So my expectation is , Assume if user enter the L then i want the first result as start with L row as out ... and if they enter LI then result must lilliput somthing like that.... Am fething columns are postcodename and postcodeCODE ...... IIn the search, the first preference must be field *postcodename *if no keyword match in the first column then go second column *postcodeCODE * ..... Can u please tell me How to write query for this siutaion.... Thanks
The query is hard to write because you are not leveraging the design qualities of a Relational Database. MySQL does not index every term of a list of values that are stored within a single field. It indexes the entire field.
MySQL (or any other RDBMS) can index a list of values stored one-at-a-time in separate rows of data. This is called "normalization" and it can not only improve your search performance but it can also reduce your storage footprint.
I suggest you modify how your data is stored by implementing the techniques of normalization. I think it will help get you started.
-- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
