performance questions: server side processing with SQL fulltext (FTS) search

tefdat

Hi,
right away - dont know, how to create a test case with a an sql server behead. Please forgive

I have a database with about 400k rows / 21 columns. Client side processing was not an option anymore.

I got into the server side processing... After loong research and a lot of trials figured out, that the "default" search does not provide any "smart search" respectively, that cant be so easily done with SQL due performance reasons.
The smart search in the client side processing is really great; a big downside without it actually..

So, after a detour through elastic search and SOLR i finally stopped again with buildin server side processing - i figured out, how to create a fulltext index in SQL and what was necessary to get it run with the ssp.

Just for testing; i created a fulltext index for three columns with SQL:
Alter table MyDb.MyTable add FULLTEXT(col1, col2, col3)

A query in SQL is done by e.g (usually it takes just a few miliseconds.:
SELECT description FROM MyDb.MyTable Where Match(col1, col2, col3) Against ("+Motor +Curre*" in boolean mode)

I removed the loop in the function 'filter' in SSP.class.php. Replaced through:
$binding = self::bind( $bindings, '%'.$str2.'%', PDO::PARAM_STR );
$globalSearch[] = "match(col1, col2, col3) against (".$binding." in boolean mode)";

Fullcode:
http://sandbox.onlinephpfunctions.com/code/7a5eb1e17f7e120ae26973f3557bbf5ffcc96d4b

The capability of SQL FTS is for my purpose currently sufficient.

Everything running flawlessly - except the speed; performance unfortunately.
A sql query does take just a few miliseconds, where a search query with datatables takes up to 6seconds.. 4,5seconds waiting time.

The server (localhost) is performant. No RAM/CPU issues at all. They are just bored.

So, any hints for the bottle neck?

tefdat

Huuu,
may I answer my question myself?

For test purpose I am using the windows xamp.

Just changed the default amazing modest settings in my.ini

[mysqld]
innodb_buffer_pool_size=40M
[mysqldump]
max_allowed_packet=16M
[isamchk]
key_buffer=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=20M
sort_buffer_size=20M
read_buffer=2M
write_buffer=2M

to following:

[mysqld]
innodb_buffer_pool_size=4048M
[mysqldump]
max_allowed_packet=512M
[isamchk]
key_buffer=1028M
sort_buffer_size=4048M
read_buffer=2048M
write_buffer=2048M

[myisamchk]
key_buffer=1028M
sort_buffer_size=4048M
read_buffer=2048M
write_buffer=2048M

TTFB decreased to 300ms
Great - its perfect now!

allan

Wow - that's a serious improvement. Nice one! And thanks for writing back with the update.

Allan