What's the cost of an SQL query?

Question

I am working on a website which could be used to simplify communication between teachers and students at our university. Let's say that about 1000 people could be using the final version. The problem is that I had not known PHP, MySQL or AJAX before and I am learning all of these on this project.

I'd like to write a live search script simplifying the input of some fields. For example, when one is about to insert a name of a student, I'd like the website to offer a list of suggestions in real time. That means a short HTTP request & response every time a key is pressed.

My question is whether it is OK to call a SQL query for each of this requests, or whether I should try and cache the results in a PHP variable somehow. The query would need to provide some extra information, so it would probably also include a left join and sorting in two columns. The size of the tables is about the number of people I mentioned above.

The web server is running on a PC, used also for other purposes, namely running demanding mathematics software packages, so I should watch the load my scripts demand.

Answer 1

PHP is server side processing. That means that every time a key is pressed you would have to send the results to the server and wait for the sql response to come back before getting the next key. There is also the issue of putting the form back as it was when the key was pressed.



If you have to do this then it will have to be done in RAM using javascript. Populate the data initially using PHP to give a unique list of names. You will have to work out how efficient this is going to be.

Assuming that there are only going to be 1000 names then it may not be so bad.

I would suggest you calculate the possible number of names for each key stroke and only display the list if it is say less than 7 or 8 names.



Have fun.

Answer 2

A very bad way to work. And storing results for that many entries is going to totally kill RAM. Enough to possibly bring down the server. Once people are entered as data you should not need to enter the names, or a lot of other data, you just create drop list for entry for any field search.

Answer 3

@Jan Dvorak - What you have reported isn't somewhat authentic. mutually as your answer is surely perfect, @Martijn answer too is an ideal one. in all probability, he wrote it in keeping with MS sq. Server syntax mutually as yours is meant for various DBMS, consistent with threat MySQL or Oracle. MS sq. Server mandates the use sq. Brackets to make a string eligible to be a column call. There are various different regulations which includes this. the two one in all your innovations are valid. One has to comprise team via clause of their question to compute the count variety of data, in each classification as reported in team via with HAVING as elective. -- In 'recommendations'... Lonely Rogue.