Query optimization (OR based)

Question

I have googled but I can't find answers for these questions. Your advice is appreciated.

centOS on vps with 512MB RAM, nginx, php5 (fastcgi), mysql5 (myisam, not innodb). I need to optimize this app created by some ex-employee. This app is working, but it's slow.

Table:

t1(id[bigint(20)],c1[mediumtext],c2[mediumtext],c3[mediumtext],c4[mediumtext])
id is some random big number, and is PK

Those mediumtext rows look like this:

c1="|box-002877|"
c2="|ct-2348|rd-11124854|hw-3949|wd-8872|hw-119037736|...etc.. "
c3="|fg-2448|wd-11172|hw-1656|...etc.. "
c4="|hg-2448|qd-16667|...etc."
    (some columns contain a lot of data, around 900 KiB, database around 300 MiB)

Yes, mediumtext "is bad", and (20) is too big... but I didn't create this.

Those codes can be found on any of those 4 mediumtext's...

//he needs all the columns of the row containing $code, so he wrote this:
function f1($code) {
SELECT * FROM t1 WHERE c1 LIKE '%$code%' OR c2 LIKE '%$code%' OR c3 LIKE '%$code%' OR c4 LIKE '%$code%';

Questions:

Q1. If $code is found on c1... mysql automatically stops checking and returns row=id+c1+c2+c3+c4? or it will continue (wasting time) checking c2, c3 and c4?...

Q2. Mysql is working with this table on disk (not RAM) because of the mediumtext, right? is this the primary cause of slowness?

Q3. That query can be cached by mysql (if using a big query_cache_size=128M value on the my.cnf)? or that's not cacheable due to the mediumtexts, or due to the "OR LIKE"...?

Q4. Do you recommend rewriting this with mysql's INSTR() / LOCATE() / MATCH..AGAINST [FULLTEXT]?

Answer 1

Q1: No, it will continue to search thru all the fields. If you use Select top 1 * from ..., that will stop from looking into the other fields.

Q2: That depends on your mySQL config, it is probably a cause of slowness but it is not probably the main cause.

Q3: An OR Like function will always be slow, and on big texts, it will be slower, you cannot do anything about it. I recommend you look for another way, and get rid of those LIKE statments, prefer a = sign.

Q4: No, it will make things slower.

Answer 2

If $code is found on c1... mysql automatically stops checking and returns row=id+c1+c2+c3+c4? or it will continue (wasting time) checking c2, c3 and c4?...

Yes.

Mysql is working with this table on disk (not RAM) because of the mediumtext, right? is this the primary cause of slowness?

No, MEDIUMTEXT is cached by the filesystem as well as the other datatypes. MyISAM does not support data page caching.

That query can be cached by mysql (if using a big query_cache_size=128M value on the my.cnf)? or that's not cacheable due to the mediumtexts, or due to the "OR LIKE"...?

MySQL caches the queries verbatim. If you change anything in your query (including case of the statements and of course the values being searched for), this query will be treated as a cache miss and executed. MEDIUMTEXT queries are cached as well as the other queries.

Do you recommend rewriting this with mysql's INSTR() / LOCATE() / MATCH..AGAINST [FULLTEXT]?

FULLTEXT indexing will certainly speed up the queries. Make sure, though, that the values you search for contain no word separators.

Answer 3

For best results you should explode those text fields out into one or more tables and create some many-to-many joining tables in between.