3manuek

explain  select  *
                      from bookContentByLine
                      where match(content) against ("+home" IN BOOLEAN MODE)
                      ORDER BY FTS_DOC_ID  
                      LIMIT 10\G
                      
  select_type: SIMPLE
        table: bookContentByLine
         type: fulltext
        Extra: Using where; Ft_hints: no_ranking; Using filesort  

3manuek

(ftslab) > select *
          from information_schema.INNODB_FT_INDEX_TABLE
          WHERE lower(WORD) like '%country%';
+------------------+--------------+-------------+-----------+--------+----------+
| WORD             | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------------------+--------------+-------------+-----------+--------+----------+
| country          |          149 |         787 |        28 |    733 |      265 |
| country          |          149 |         787 |        28 |    733 |     1342 |
| countrydistricts |          733 |         733 |         1 |    733 |      816 |
| thecountry       |          249 |         733 |         2 |    733 |      750 |

3manuek

CREATE TABLE bookContentByLine_stopwords(value VARCHAR(30)) ENGINE = INNODB;

INSERT INTO bookContentByLine_stopwords
SELECT value FROM (
    SELECT value FROM
    INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
    UNION
    SELECT DISTINCT WORD as value  
      FROM information_schema.INNODB_FT_INDEX_TABLE               
      GROUP BY WORD having count(*) > 1000

3manuek

(ftslab) > select group_concat(WORD) FROM (select distinct WORD  
  FROM information_schema.INNODB_FT_INDEX_TABLE               
  group by WORD having count(*) > 1000) d\G
*************************** 1. row ***************************
group_concat(WORD): all,and,any,been,but,can,first,had,has,have,her,him,his,into,
its,like,may,more,nor,not,now,one,only,other,our,said,shall,she,should,some,such,
than,their,them,then,there,these,they,those,thou,thus,thy,time,were,which,would,
yet,you,your
1 row in set (5,28 sec)

3manuek

SELECT WORD,count(*)
           FROM information_schema.INNODB_FT_INDEX_TABLE   
           group by WORD having count(*) > 1000
           order by 2
           limit 10;
           
+--------+----------+
| WORD   | count(*) |
+--------+----------+
| should |     1023 |

3manuek

SET GLOBAL innodb_ft_aux_table = 'ftslab/bookContent';

SELECT bookid, FTS_DOC_ID,
                group_concat(it.POSITION) as positions,
                round(MATCH(content) AGAINST ("country" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)) as QERank,
                round(MATCH(content) AGAINST ("country" IN BOOLEAN MODE)) as BooleanRank,
                length(content) as len
          FROM bookContent bl join information_schema.INNODB_FT_INDEX_TABLE it ON (bl.FTS_DOC_ID = it.DOC_ID)  
          WHERE  MATCH(content) AGAINST ("country" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)
              AND it.WORD = 'country'

3manuek

set global innodb_ft_aux_table = 'ftslab/bookContentByLine';

SELECT  content, bookid, group_concat(it.POSITION) as pos,
        round(MATCH(content) AGAINST ("country" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)) as QERank,
        round(MATCH(content) AGAINST ("country" IN BOOLEAN MODE)) as BoolRank
      FROM bookContentByLine bl join information_schema.INNODB_FT_INDEX_TABLE it
          ON (bl.FTS_DOC_ID = it.DOC_ID)  
      WHERE  MATCH(content) AGAINST ("country" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)
          AND it.WORD = 'country'
     GROUP BY FTS_DOC_ID

3manuek

➜  EC2 git:(master) ✗ cat .create_ec2_tutorial 
# Default variables

# tag and description of the instances
tagsName="mysql"
tagsValue="breakfixlab"
KEY=breakfixlab
IMAGE=Breakfixlab_PL16
INSTANCE_SIZE="m1.small"

3manuek

Part I

Slide 06
sudo su -
service mysql start
grep ERROR /var/log/mysqld.log | tail
!ps # Alias for `ps aux | grep mysql`

Slide 07
grep tmpd /etc/my.cnf

3manuek

                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=136.84..136.84 rows=1 width=168) (actual time=675.113..675.114 rows=9 loops=1)
   Sort Key: (ts_rank(ls._fts, query.query))
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=57
   ->  Nested Loop  (cost=100.15..136.83 rows=1 width=168) (actual time=606.784..675.068 rows=9 loops=1)
         Buffers: shared hit=57
         ->  Nested Loop  (cost=100.00..129.96 rows=1 width=186) (actual time=594.691..595.077 rows=9 loops=1)
               Join Filter: (ls.id = rds.id)