Query optimization (OR based)
- by john194
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]?
