sphynx | A light & simple WordPress starter theme | Theme library

The culprit of the issue you raise seems to be the line STRING=$(paste $file1.breed.txt)

because the file *.breed.txt you are interested in is taken in one go, instead of looking at each individual line.

So I'd suggest using a Bash array that you'd populate with this file (but this is obviously not the only possible solution):

FULLTEXT is very efficient, even for a single word. It becomes inefficient if there are thousands of rows with the search term(s) ('office' in your example).

It will not be doing a "full table scan". If you are seeing such, please provide EXPLAIN FORMAT=JSON SELECT ... so we can dig further into it.

LIKE with a leading wildcard (eg, `LIKE '%office%') is very slow because it will do a table scan. (Actually, in your case, ignore the search for "office" and use the other index.)

I hope the table is InnoDB, not MyISAM. Note: the old "post" is talking about MyISAM.

The Fulltext index will be used and the other index(es) will not.

Trying to make use of both indexes (via subqueries, joins, or whatever) is unlikely to help.

re Update 1

If the numeric columns filter down to a fraction of a percent of the rows, that leads to a quandry.

• Doing that filtering first leads to difficulties in doing the FT filtering second. (I don't know if it even possible.)
• Doing LIKE '%office%' would require checking all 10K rows. If the 10K is sometimes 1M, this will be too costly.
• Similarly, if MATCHing 'office' leads to 1M rows, the secondary filtering is probably too costly.
• RLIKE provides more power, but even less speed; I see not advantage in considering it.
• PARTITION BY system_id and/or status may be viable. What is the distribution of those columns? If one value of either has 1M rows it won't help much when picking it. Do you always use = when testing system_id and/or status? Or maybe IN? Or a range?

If the distribution is 'reasonable', here's how PARTITION would work.

• We would need to figure which column (system_id or status) to partition on. This would be based on the frequency of each and how many different values there are.
• We would need to figure out how many different partitions to have -- too many would lead to a different inefficiency.
• The Optimizer would first do "partition pruning" on the "partition key" (one of those two columns). Within that partition would be a separate FULLTEXT index that sees only that partition.
• I'll be more specific when I hear about the distribution of the columns.

(I do not know enough about Sphynx or ElasticSearch to comment. A caution: How big is the entire dataset? How much RAM do you have?)

As for having only INDEX(system_id, status), then using LIKE -- this might be good. I would find extreme cases of each of the columns and run tests to see how slow or fast each query formulation is. A dozen test cases might be easy to write and test. I think that you will find that each query formulation (including Rolando's) will work well for some value of system_id, status, and text, and poorly for others. The partition approach may make the worst cases less bad, while not hurting the fast cases.

FULLTEXT is faster than LIKE because FT build an inverted index of word-->row, while LIKE must scan each row each time.

re Update2

If status is almost always 900, then handle the non-900 cases differently.

Plan A:

It sounds like you didn't define which parts of your documentation from docstrings should be considered by Sphinx. For example, it is assumed that you have a file ./docs/sourc/index.rst which contains something like:

You need co-varience:

Take note that this means that you have to create a collection of the interface IDiet

Define a method in your class named hurt and a getter for health, like this :

Some databases do integer division, so 1/2 is 0 rather than 0.5. If this is the case, then your numbers will be off.

It is easily fixed. I just multiply by 1.0:

The word return is missing in the "getBreed" method