You can do the search into the two fields by using only one query

select * from table where Title like '%texttofind%' OR Subtitle like '%texttofind%'"

Then if you are really concerned about the speed, replace * with the field you need

will replacing * with that field have a major effect on speed?

Yes, absolutely !

Take the habit to replace * (which retrieves all fields) with "SELECT id, title " (that is only what you need)

But using a like statement over 200 000 records can be really slow (luckily you varchar is small).

yeah, i know that like '%text%' is slow, and it makes me wonder about doing this:
looking for 'text_to_search%', and if the text wasn't found,
running a second query with '%text_to_search%'.
shouldn't this work faster (assuming that most people will look for something that exists) ..

replacing the * won't save any significant time on the actual search on the database server, as you are only searching on the two fields not all of them.

you should of course only select the fields you want returned (and not *) because this cuts down on passing data between the mysql server and your application to what is needed... this does of course save bandwidth and possibly time.

you should set a full text index on each of the varchar columns you want to search in - this will speed things up considerably.

regarding wether you should 'merge' the 2 columns, technically you would have to do 2 passes to search 2 fields, which is longer, but the single field would take longer to search, so only testign would give the answer in your case.

setting the full text index on both fields (or the single field if you merge them) will make a significant difference.

additionally are you looking for an exact phrase match or the words in any order?

as these are different!

%text_to_search% is looking for an exact phrase match somewhere in the filed

to search for the words in any order you would need to do something like:

LIKE %text% AND LIKE %to% AND LIKE %search%

i'm looking for an exact phrase .

by the way, i have another question :
my fields may be include both lower and upper case letters (all in english), and i don't really care about that .

so is it better to convert everything to a unified lower/upper case letters, so that mysql will have a better chance of finding a certain expression faster ?

and if it does make the queries run faster, is it negligible or does it have a major performance improvement?

i don't think it will make any difference if you make the whole field lower case.

as i said above the single BIGGEST improvement you can make is by putting a full text index on the columns you wish to search, you can do this in phpmyadmin i think.

i know it's the biggest improvement, but i'm looking for other, minor, improvements as well :)

and another thing - how faster is a unique index than a regular index ?

there are lots of factors, including table sizes ...
in tables with very few rows then having no index at all will be quicker.

if you are looking for minor tweaks then you need to ask questions like:
am i doing updates and inserts or just look-ups? depending on the proportion of the two then different types of table work better.

i would suggest you import your database onto your local machine, and use mysql query browser to test different indexes or whatever, you can run queries and it tells you how long they took ... try it with different indexes.

[it is not a perfect way to test but it is a good way]

with databases there is rarely a definative/universal fastest way, it can be very specific to your circumstances.

remember too that there is likely a network bottleneck between the mysql server and the webserver, which will slow your application down anyway (especially in a shared hosting environment)

my case is this :
~100k-150k records
user can only search for records on my table.
the only one who's updating the table (INSERT's only) is me, and i do an INSERT of ~100 records once a day, every day .

there string fields i am doing searches are two varhchar(150) .