Hi.

I'm wondering what's the best way to index a table to speed up ajax autocomplete queries. Let's say we have a table 'movies' with a single column 'title':

"one of the movies"
"super movie one"
"super movie two"
"super movie three"
"bad movie one"
"bad movie two"

When a user types in "supe", tips should show all movies starting with a string "supe", but if user types in "one", the tips should show the first movie as the most appropriate and then two movies containing word "one" at the end.

I've read in mysql documentation that BTREE indexes can index for queries like "LIKE 'keyword%', but not for "LIKE '%keyword%'". So I thought that maybe such thing would work: create index on that column of type BTREE, then on the same column a full text search index. The query would look like this:

SELECT title FROM movies
WHERE title LIKE 'keyword%'                   #use INDEX
OR MATCH (title) AGAINST('*keyword*')         #use FULLTEXT

My questions concerning the above are:
- can mysql use both indexes in one query?
- how to order the results so that ones that start with a keyword (the first condition) come first?

I've read this thread: http://phpbuilder.com/board/showthread.php?t=10361515 and I have some things to do on the javascript side of things to limit queries but some tips on shortening query time would be helpful as well.

    I'd try it by making two SELECT statements and UNION the results

    http://www.mysqltutorial.org/sql-union-mysql.aspx

    You can sort the results of the union with a dummy column. This is untested but should give you the idea. It'll return duplicates if both SELECTs match a title, you'll need to filter them out somewhere else...

    SELECT 1 as sortOrder, title 
FROM movies
WHERE title LIKE 'keyword%'                   
    
UNION
SELECT 2 as sortOrder, title 
FROM movies
WHERE MATCH (title) AGAINST('*keyword*') 
ORDER BY sortOrder, title

      Another option would be to move indexing from the db to an index external to the db, such as sphinx

        Write a Reply...