Searches and Indices

blindfish

I'm fairly new to this whole PHP business and not a regular programmer... but I've been making good use of Dreamweaver MX to get online database stuff working.

I'm currently looking into the best methods to search a database and one of the things I looked into is creating 'search forms' using drop-down boxes populated from within the database... I'm using PHP to filter out duplicates and then sort the list and I've got it to the point where it works very much like Excel's autofilter.

I actually envision it being used to filter down infrequent values to speed up searching... So for example in a search for a university tutor, having a page that filters by faculty and department and then carrying out the search for a particular name on the results... maybe I'm barking up the wrong tree?

The obvious problem with this method is that as soon as the database starts getting larger the whole thing is likely to grind to a halt as the PHP does its work to populate the drop-downs.

A colleague suggested that indices might help... If I understand correctly setting a column as an index effectively produces a separate hidden table with an equivalent 'column' with duplicates removed and the values sorted - which is then used to optimise searches. (Please correct me if I'm wrong!)

My question is: is there a way of importing the values in the index to populate my drop-downs?

steadyguy

Depends on your database ... PostgreSQL allows you access to the indices, but they're made for being read by a machine, and not a person.

In any case, based on your description of you project, I don't see the need to access the indices directly; it wouldn't be any faster. Databases will avoid full table scans if at all possible, that is, if an index is available. An index - or if you're doing multiple sorts, indices - will greatly speed up the query. Index as many columns as necessary - viz., the ones you're searching against. Also, try to use queries with fewer conditions if at all possible, as the more conditions that must be met, the longer the search will take. Using the OPTIMIZE statement occasionally, will also help.

blindfish

Sorry - should have said I was using MySQL...

I was actually thinking of using the index as a shortcut to populate the dynamic dropdowns... the PHP script I'm using takes all the values in a column, dumps them into an array, runs unique_array and then sorts them. When a user changes the value in a dropdown the form is submitted and the database is searched with the selected value, thereby filtering it by that value...

I'm really tinkering at the moment and don't have a pratical application for this - just learning really...

Anyway, my point was that if the values in the index are already sorted and filtered for duplicates (which I'm actually not sure about) then it would seem I'm duplicating this activity with my script and it's the script that I think will slow everything down... maybe not if I index the column?

I was considering storing the values for the dropdowns in a separate table somehow automatically populated in the MySQL database... but then a colleague made this comment about using indices.

Cheers...

steadyguy

You are more or less right about the way an index works ... but when you submit a query (say, SELECT DISTINCT('items') FROM itemList), provided the column , 'items' is indexed, MySQL will automatically use the index: it knows this is much faster than going through each item and comparing it with all the others, and will basically query the index for you. All you need to do is index the appropriate columns, and let the database do the rest for you.