facebooks friend search vs. my search

[mikefrederick]

When you use facebook's friend search, there is no time loading friends that match the search criteria in between pressing keys. I tried to make something like it at notesite.truegraphic.com. Search for University or something. I use AJAX and PHP with a query like this:

 

mysql_query("select * from schools where name like '%".$name."%' order by name limit 7") or die(mysql_error());

 

 

But everytime you press a key all of the schools go away and take too long to come back. How could I cut the time down? There are 500 schools in the database, do you think it is just taking a long time to search them all?

[mikefrederick]

anyone?

[mikefrederick]

help

[haku]

Facebook probably has really fast servers, and lots of them. Its a multi-million (billon?) dollar organization. It also depends on your code - if your code is well written it will execute faster than if its poorly written (note: I haven't seen your code, so i don't know which it is).

[aschk]

Have you run a profile trace on this query (like 1000 times)

select * from schools where name like '%".$name."%' order by name limit 7

 

Have you put an index on the name column?

 

As far as I am aware most SQL statements that have "LIKE '%something%' " in them aren't the most efficient. However, I can't see a way to reduce that query with a better statement, as obviously you need to cover "California University", "University institute of New Mexico" and "Google University of Nebraska", each being a different scenario.

 

Do an EXPLAIN on that query to see if/what indexes it is using.