Yola

Could anyone else comment please? I am curious as to the common practices for searching and splitting tables.

Thank you ever so much for that thorough answer. That was most useful. I guess I was looking to confirm or debunk methods that colleagues had recommended to me in the past before committing any as my own practices. For example, I had been advised for text searching to never use LIKE queries on large tables as I had been instructed they can take too long to run and can affect the whole system. Therefore since my colleagues actually perform searches similar to example you listed but by grabbing all the table rows in PHP and searching there (and assigning different keywords points to order the matches) I was particularly interested to hear that also should remain a MySQL query. The reason they had given me for this technique was that a single SELECT * was quite instantaneous and would have less of an impact on the database than checking every row when the data was also being regularly updated by other concurrent users. With regard to splitting tables, the concept that was recommended was one in which a user with a username robert would have his record stored within either users_r or users_ro (depending on whether 26 or 676 tables was more a efficient split) and within that table he might have userid 14287. This would mean the PHP queries would be of the form: "SELECT * FROM users_".substr($username, 0, 1)." WHERE username='".$username."'"; I was advised that this would reduce access times considerably and again reduce the impact any one query would have on any others running.

Okay, cheers Update: So are there 'any' instances where it be more efficient to grab all the data (and move the processing to PHP) in a large scale frequently accessed system? I've seen it done for some search cases so it must be beneficial sometimes? and, Would it be appropriate to split up a data table with minimum tens of thousands of rows to speed up data access. e.g. By splitting an users tables into 26 tables (users_a, users_b, users_c, ... users_z) or even into 676 tables (users_aa, users_ab, ... users_ba, users_bb, ... users_zzz), or is this bad practice?

Thank you for that info So only for partial search matching across different fields or something more complex would it be worth doing the processing in PHP then? Do you have thoughts on my second question on database structures please, the benefits or issues with splitting tables into sub-tables (breaking 'table' into 'table_a' to 'table_z' or 'table_aa' to 'table_zz')?

Thank you for your reply. That is interesting.. In which cases then is it more efficient to grab all the data to search through in PHP?

Hi, I was wondering if I could ask at what point it is more efficient to search in PHP rather than MySQL when querying data from the database please? i.e. If a database has tens of thousands of entries/rows per table and plenty of concurrent users accessing the data is it better to A) use a straight MySQL query: SELECT tablecolumn2 FROM tablename WHERE tablecolumn1='1234'; B) to grab all the rows and then sort 'and' search the data in PHP using: SELECT tablecolumn2 FROM tablename; or C) to sort and grab all the rows and then search the data in PHP using SELECT tablecolumn2 FROM tablename ORDER BY tablecolumn1; As a second question, would it be appropriate to split up a data table with minimum tens of thousands of rows to speed up data access. e.g. By splitting an users tables into 26 tables (users_a, users_b, users_c, ... users_z) or even into 676 tables (users_aa, users_ab, ... users_ba, users_bb, ... users_zzz), or is this bad practice?