Started 1 week, 1 day ago (2009-11-18 19:37:00)
by Rick James
Two-dimensional search is difficult.
It is "Using index". First it filters on measure1, but then it has to check each measure2.
How wide of a band are you typically searching? In this case you have "10.76 AND 20.91"; is this typical? What percentage of the total range is that? (I'm checking on some details before launching into a complex scheme to give you possibly 10x performance ...
Started 1 week, 1 day ago (2009-11-18 19:03:00)
by Rick James
Elaborate
*
Schema; # rows
* Machine size
etc
Started 2 weeks, 3 days ago (2009-11-10 01:24:00)
by kavitha L
CREATE TABLE `yp_companies` (
`company_id` int(11) NOT NULL default '0',
`company_name_en` varchar(100) NOT NULL,
`company_name_ar` varchar(100)
character set utf8 NOT NULL,
`address_en` varchar(200) NOT NULL,
`address_ar` varchar(200) character set utf8 NOT NULL,
`pobox_name_en` varchar(100) character set utf8 NOT NULL,
`pobox_name_ar` varchar(100) character set utf8 NOT NULL,
`...
Started 3 weeks, 4 days ago (2009-11-02 07:14:00)
by Emilio Nicolás
Aditional information:
*
Materialized view table is MyIsam engine
* users table is InnoDB engine
Any more information you need?
Thanks!
Started 3 weeks, 6 days ago (2009-10-31 10:19:00)
by Rick James
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 ...
Started 4 weeks ago (2009-10-29 22:08:00)
by Rick James
SHOW CREATE TABLE inbox \G
SHOW TABLE STATUS LIKE 'inbox';
SHOW VARIABLES LIKE '%buffer%';
ALTER TABLE inbox
ADD INDEX(IDPerso, display, msg_size);
Started 4 weeks, 1 day ago (2009-10-28 22:12:00)
by Rick James
*
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
...
Started 1 month ago (2009-10-24 20:02:00)
by Rick James
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.
Started 1 month ago (2009-10-25 10:11:00)
by Rick James
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.
Started 1 month ago (2009-10-23 22:05:00)
by Rick James
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 ...
Size: 594 bytes
