Quote:
|
Originally Posted by mocremilo
Is there any way I can select the entire column, store it in an array, and have php do the work of counting the appearance of the substrings within the array?
|
Absolutely you could. You could even implode() the array so that you're simply counting occurrences in one big string and loop your substrings through substr_count() - beware though - if you're searching for very short substrings this would be could potentially create false positives and it would be safer in that case to keep them as an array.
I'm not sure I'd use FULLTEXT for this. It's useful for natural language searching, or if you wanted to create a mini search engine, but in this case I'm not sure it would provide much assistance.
Just as a quick aside before this goes any further, you're not simply counting the number of occurrences of every different existing value in a column are you? If so, you could do it with a simple SELECT DISTINCT query. I thought it was worth checking before considering more elaborate options.
One other thing that would help to work out the most efficient method is to get a rough idea of what kind of size of database we're talking about here. I'm working on a project at the moment where one of the tables grows at the rate of 30,000 rows a week, so we're dealing with literally row counts in the millions. MySQL is comfortably handling fairly complex queries against tables of this size.