Optimizer | Forum profile

Aditional information: * Materialized view table is MyIsam engine * users table is InnoDB engine Any more information you need? Thanks!

Executive summary: * New index * Increase cache size * Shrink data Details... That particular query would benefit from either of these: INDEX (client_id, cashin_ind, annual_flag, date_record), INDEX (client_id, annual_flag, cashin_ind, date_record), (There is not advantage in having both.) I don't think you can get much better than simply adding one of those. INDEX ...

SHOW CREATE TABLE inbox \G SHOW TABLE STATUS LIKE 'inbox'; SHOW VARIABLES LIKE '%buffer%'; ALTER TABLE inbox ADD INDEX(IDPerso, display, msg_size);

* Normalization * Compression Normalization: Look for any columns that have a lot of duplicate values (and that are longer than a few characters). Build another table(s) with those colunns in them, together with an AUTO_INCREMENT id (perhaps a SMALLINT UNSIGNED or MEDIUMINT), replace the field(s) with that id. A suggestion on finding likely candidates: CREATE TEMPORARY TABLE foo ...

You will be looking up by A, not B, correct? InnoDB, with PRIMARY KEY(A). No advantage for splitting the table. Please do SHOW CREATE TABLE so I can check for other issues.

Please show the actual info; I suspect there a subtle item that was filtered out in your summary of the situation. * SHOW CREATE TABLE tbl\G * SHOW TABLE STATUS LIKE 'tbl'\G * EXPLAIN SELECT ...\G * SHOW VARIABLES LIKE '%buffer%'; -- cache size and surround them with [ code ] and [ / code ] In particular, I would have expected "ref=const,const"; so I suspect something is amiss.

The relevant items: 2GB RAM IIS also running skip-innodb key_buffer_size=290M Deduced: Multiple programs running on same machine This is Windows Recommend shrinking several things: key_buffer_size=150M max_connections=100 -- unless you really need lots of connections query_cache_size=20M -- or query_cache_type = OFF thread_cache_size=5 table_cache=400 SHOW ...

Two issues... * The 7 is just an estimate based on an unknown number of rows to expect from the subquery. (Yeah, it possibly could have done better.) * MySQL often does a poor job of optimization in two situations -- subqueries and "(x,y) IN ...". Both of those exist in the last query. It will actually do a full table scan (see "ALL") of your 200K rows. Suggest you not use the last ...

Why? MySQL decided to do a table scan when it estimated that you needed more than some threshold % of the table. What to do? There is no good answer. Do you really need to fetch 2,038,247 rows on a regular basis? OPTIMIZE TABLE discount_transactions_iptraffic_all; might help some. But it would lock the table for hour(s).

Executive summary: * New index * Increase cache size * Shrink data Details... That particular query would benefit from either of these: INDEX (client_id, cashin_ind, annual_flag, date_record), INDEX (client_id, annual_flag, cashin_ind, date_record), (There is not advantage in having both.) I don't think you can get much better than simply adding one of those. INDEX ...

Aditional information: * Materialized view table is MyIsam engine * users table is InnoDB engine Any more information you need? Thanks!