MySQL uses the index that fits best to perform the query and the decision is made on the cardinality of the index. Indices with a higher cardinality are preferred over indices with lower ones. Problems arise if the cardinality is incorrect and especially the mshop_index_text MyISAM table containing the fulltext index is known for being vulnerable to this. Therefore, it's a good idea to check the cardinality values in the mshop_index_* tables once in a while and especially if you think the product search is slower than in the past while the number of products is not much higher than before.
To get rid of the problem you have to execute the MySQL
ANALYZE TABLE "table name"
statement and if this doesn't help, run the MySQL
OPTIMIZE TABLE "table name"
statement afterwards. The statements are also executed after the index rebuild has been finished.
Please be careful with "OPTIMIZE TABLE" if your shop has a large number of records in these index tables as it hurts performance of the front-end during its execution quite drastically.
Depending of the size of the database and the number of rows in the different tables, it might be necessary to adapt some MySQL server settings to get the maximum speed out of Arcavias.
The easiest way to find out if there are any problems caused by e.g. buffers that are too small is the "Status" tab (or "Show MySQL runtime information" link in older versions) on the home screen of phpMyAdmin. It contains the different server variables that can be changed, their value and a description sometimes including hints what to do. The cool thing about this view is that phpMyAdmin highlights the values in red that may be a source of problems.
Especially watch out for these keys:
These MySQL variables are worth looking for:
If you have enough RAM, you should try to keep all indexes in memory to reduce the need to access the hard disc to a minimum. Nevertheless, make sure that key_buffer_size + innodb_buffer_pool_size does not exceed 75% of the available RAM and that there's enough RAM for the database connections and the operating system.
To find out the size (in MB) of all indexes, use this statements:
SELECT CEIL( SUM( index_length + data_length ) / POWER( 1024, 2 ) ) innodb_buffer_pool_size_MB
FROM information_schema.TABLES WHERE engine = 'InnoDB';
SELECT CEIL( SUM( index_length ) / POWER( 1024, 2 ) ) key_buffer_size_MB
FROM information_schema.TABLES WHERE engine = 'MyISAM'
AND table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' );
To get the maximum performance that is possible it's necessary to reduce the need to access the hard disc to a minimum. For this, you must have enough RAM to store the indexes of the mshop_index_* tables into the MySQL cache.
For InnoDB tables, the OPTIMIZE TABLE statement makes sure that the complete indexes (and data as InnoDB stores both in one data structure) of the tables are in the cache if it's big enough:
OPTIMIZE TABLE mshop_index_attribute
OPTIMIZE TABLE mshop_index_catalog
OPTIMIZE TABLE mshop_index_price
There's another statement required the MyISAM table for the texts, that contain the fulltext index:
LOAD INDEX INTO CACHE mshop_index_text
If you have plenty of RAM in your database server, you can also load the mshop_product and mshop_product_list tables:
OPTIMIZE TABLE mshop_product
OPTIMIZE TABLE mshop_product_list