# Percona MySQL 5.1.59-rel13.0-log Performance Issues
**URL:** https://forums.percona.com/t/percona-mysql-5-1-59-rel13-0-log-performance-issues/1808
**Category:** Other MySQL® Questions
**Created:** [May 3, 2012, 8:45am UTC](https://forums.percona.com/t/percona-mysql-5-1-59-rel13-0-log-performance-issues/1808 "2012-05-03T08:45:12Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@JohnG](https://forums.percona.com/u/JohnG)
#### Post date: [May 3, 2012, 8:45am UTC](https://forums.percona.com/t/percona-mysql-5-1-59-rel13-0-log-performance-issues/1808/1 "2012-05-03T08:45:12Z")
Although I’ve been a DBA with MS-SQL for over 15 years, I can consider myself a newbie with MySQL Administration.
All tables use INNODB and are pretty large… one table have over 250 million rows while other big tables vary between 6 and 12 million rows.
I use navicat for query tasks… and linux for admin tasks.
I have a query that runs on a daily basis that eats up my memor and starts swapping to disk. This query can take over an hour to run and returns around 2.8 million records.
I’ve tried to optimize the query, all of last week, which didn’t help.
It seems that the problem lies in some MySQL setting… Am at the point where I’m just confused and nothing makes sence anymore…
Can some just tell by looking at my settings if there’s an obvious problem?
We’re on a cloud and available memory is 24GB.
HD space isn’t an issue.
I’ve tried to change a few variables, restarted MySQL, I’ve added the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
and the SQL\_NO\_CACHE…
Nothing worked… What I’m looking for is to lower memory consumption and speedup my query!!!
I’m pretty confortable with SQL scripts (although I don’t necessary love the EXPLAIN for analyzing - I guess it will have to do)
here are my variable settings:
auto\_increment\_increment 1
auto\_increment\_offset 1
autocommit ON
automatic\_sp\_privileges ON
back\_log 50
basedir /usr/
big\_tables OFF
binlog\_cache\_size 32768
binlog\_direct\_non\_transactional\_updates OFF
binlog\_format STATEMENT
bulk\_insert\_buffer\_size 8388608
character\_set\_client utf8
character\_set\_connection utf8
character\_set\_database utf8
character\_set\_filesystem binary
character\_set\_results utf8
character\_set\_server utf8
character\_set\_system utf8
character\_sets\_dir /usr/share/mysql/charsets/
collation\_connection utf8\_general\_ci
collation\_database utf8\_general\_ci
collation\_server utf8\_general\_ci
completion\_type 0
concurrent\_insert 1
connect\_timeout 10
datadir /mnt/mysql/
date\_format %Y-%m-%d
datetime\_format %Y-%m-%d %H:%i:%s
default\_week\_format 0
delay\_key\_write ON
delayed\_insert\_limit 100
delayed\_insert\_timeout 300
delayed\_queue\_size 1000
div\_precision\_increment 4
enable\_query\_response\_time\_stats OFF
engine\_condition\_pushdown ON
error\_count 0
event\_scheduler OFF
expand\_fast\_index\_creation OFF
expire\_logs\_days 14
fast\_index\_creation ON
flush OFF
flush\_time 0
foreign\_key\_checks ON
ft\_boolean\_syntax + -\>\<()~\*:“”&|
ft\_max\_word\_len 84
ft\_min\_word\_len 4
ft\_query\_expansion\_limit 20
ft\_stopword\_file (built-in)
general\_log OFF
general\_log\_file /mnt/mysql/int-btr-db2.log
group\_concat\_max\_len 1024
have\_community\_features YES
have\_compress YES
have\_crypt YES
have\_csv YES
have\_dynamic\_loading YES
have\_geometry YES
have\_innodb YES
have\_ndbcluster NO
have\_openssl DISABLED
have\_partitioning YES
have\_query\_cache YES
have\_response\_time\_distribution YES
have\_rtree\_keys YES
have\_ssl DISABLED
have\_symlink YES
hostname [int-btr-db2.beyondtherack.com](http://int-btr-db2.beyondtherack.com)
identity 0
ignore\_builtin\_innodb OFF
init\_connect SET NAMES utf8
init\_file
init\_slave
innodb\_adaptive\_checkpoint estimate
innodb\_adaptive\_flushing OFF
innodb\_adaptive\_hash\_index ON
innodb\_additional\_mem\_pool\_size 209715200
innodb\_auto\_lru\_dump 0
innodb\_autoextend\_increment 32
innodb\_autoinc\_lock\_mode 1
innodb\_blocking\_lru\_restore OFF
innodb\_buffer\_pool\_shm\_checksum ON
innodb\_buffer\_pool\_shm\_key 0
innodb\_buffer\_pool\_size 18014535680
innodb\_change\_buffering inserts
innodb\_checkpoint\_age\_target 0
innodb\_checksums ON
innodb\_commit\_concurrency 32
innodb\_concurrency\_tickets 500
innodb\_data\_file\_path ibdata1:4G:autoextend
innodb\_data\_home\_dir /mnt/mysql
innodb\_dict\_size\_limit 0
innodb\_doublewrite ON
innodb\_doublewrite\_file
innodb\_enable\_unsafe\_group\_commit 0
innodb\_expand\_import 0
innodb\_extra\_rsegments 0
innodb\_extra\_undoslots OFF
innodb\_fake\_changes OFF
innodb\_fast\_checksum OFF
innodb\_fast\_recovery OFF
innodb\_fast\_shutdown 1
innodb\_file\_format Antelope
innodb\_file\_format\_check Barracuda
innodb\_file\_per\_table ON
innodb\_flush\_log\_at\_trx\_commit 2
innodb\_flush\_log\_at\_trx\_commit\_session 3
innodb\_flush\_method O\_DIRECT
innodb\_flush\_neighbor\_pages 1
innodb\_force\_recovery 0
innodb\_ibuf\_accel\_rate 100
innodb\_ibuf\_active\_contract 1
innodb\_ibuf\_max\_size 9007251456
innodb\_io\_capacity 200
innodb\_kill\_idle\_transaction 0
innodb\_lazy\_drop\_table 0
innodb\_lock\_wait\_timeout 50
innodb\_locks\_unsafe\_for\_binlog OFF
innodb\_log\_block\_size 512
innodb\_log\_buffer\_size 33554432
innodb\_log\_file\_size 1073741824
innodb\_log\_files\_in\_group 2
innodb\_log\_group\_home\_dir /mnt/mysql\_logfile
innodb\_max\_dirty\_pages\_pct 75
innodb\_max\_purge\_lag 0
innodb\_mirrored\_log\_groups 1
innodb\_old\_blocks\_pct 37
innodb\_old\_blocks\_time 0
innodb\_open\_files 300
innodb\_overwrite\_relay\_log\_info OFF
innodb\_page\_size 16384
innodb\_pass\_corrupt\_table 0
innodb\_random\_read\_ahead OFF
innodb\_read\_ahead linear
innodb\_read\_ahead\_threshold 56
innodb\_read\_io\_threads 4
innodb\_recovery\_stats OFF
innodb\_replication\_delay 0
innodb\_rollback\_on\_timeout OFF
innodb\_show\_locks\_held 10
innodb\_show\_verbose\_locks 0
innodb\_spin\_wait\_delay 6
innodb\_stats\_auto\_update 1
innodb\_stats\_method nulls\_equal
innodb\_stats\_on\_metadata ON
innodb\_stats\_sample\_pages 8
innodb\_stats\_update\_need\_lock 1
innodb\_strict\_mode OFF
innodb\_support\_xa OFF
innodb\_sync\_spin\_loops 30
innodb\_table\_locks ON
innodb\_thread\_concurrency 32
innodb\_thread\_concurrency\_timer\_based OFF
innodb\_thread\_sleep\_delay 10000
innodb\_use\_purge\_thread 1
innodb\_use\_sys\_malloc ON
innodb\_use\_sys\_stats\_table OFF
innodb\_version 1.0.17-13.0
innodb\_write\_io\_threads 4
insert\_id 0
interactive\_timeout 28800
join\_buffer\_size 131072
keep\_files\_on\_create OFF
key\_buffer\_size 1073741824
key\_cache\_age\_threshold 300
key\_cache\_block\_size 1024
key\_cache\_division\_limit 100
language /usr/share/mysql/english/
large\_files\_support ON
large\_page\_size 0
large\_pages OFF
last\_insert\_id 0
lc\_time\_names en\_US
license GPL
local\_infile ON
locked\_in\_memory OFF
log OFF
log\_bin ON
log\_bin\_trust\_function\_creators OFF
log\_bin\_trust\_routine\_creators OFF
log\_error /mnt/logs/mysql/mysql\_error.log
log\_output FILE
log\_queries\_not\_using\_indexes ON
log\_slave\_updates ON
log\_slow\_admin\_statements ON
log\_slow\_filter
log\_slow\_queries ON
log\_slow\_rate\_limit 1
log\_slow\_slave\_statements OFF
log\_slow\_sp\_statements ON
log\_slow\_timestamp\_every OFF
log\_slow\_verbosity microtime
log\_warnings 2
long\_query\_time 1.000000
low\_priority\_updates OFF
lower\_case\_file\_system OFF
lower\_case\_table\_names 0
max\_allowed\_packet 16777216
max\_binlog\_cache\_size 18446744073709547520
max\_binlog\_size 1073741824
max\_connect\_errors 10
max\_connections 10000
max\_delayed\_threads 20
max\_error\_count 64
max\_heap\_table\_size 33554432
max\_insert\_delayed\_threads 20
max\_join\_size 18446744073709551615
max\_length\_for\_sort\_data 1024
max\_long\_data\_size 16777216
max\_prepared\_stmt\_count 16382
max\_relay\_log\_size 0
max\_seeks\_for\_key 18446744073709551615
max\_sort\_length 1024
max\_sp\_recursion\_depth 0
max\_tmp\_tables 1024
max\_user\_connections 0
max\_write\_lock\_count 18446744073709551615
min\_examined\_row\_limit 0
multi\_range\_count 256
myisam\_data\_pointer\_size 6
myisam\_max\_sort\_file\_size 9223372036853727232
myisam\_mmap\_size 18446744073709551615
myisam\_recover\_options OFF
myisam\_repair\_threads 1
myisam\_sort\_buffer\_size 4194304
myisam\_stats\_method nulls\_unequal
myisam\_use\_mmap OFF
net\_buffer\_length 16384
net\_read\_timeout 30
net\_retry\_count 10
net\_write\_timeout 60
new OFF
old OFF
old\_alter\_table OFF
old\_passwords OFF
open\_files\_limit 50000
optimizer\_fix ON
optimizer\_prune\_level 1
optimizer\_search\_depth 62
optimizer\_switch index\_merge=on,index\_merge\_union=on,index\_merge\_sort\_union=o n,index\_merge\_intersection=on
pid\_file /mnt/mysql/int-btr-db2.beyondtherack.com.pid
plugin\_dir /usr/lib64/mysql/plugin
port 3306
preload\_buffer\_size 32768
profiling ON
profiling\_history\_size 15
profiling\_server OFF
profiling\_use\_getrusage OFF
protocol\_version 10
pseudo\_thread\_id 59
query\_alloc\_block\_size 8192
query\_cache\_limit 1048576
query\_cache\_min\_res\_unit 4096
query\_cache\_size 268435456
query\_cache\_strip\_comments OFF
query\_cache\_type ON
query\_cache\_wlock\_invalidate OFF
query\_prealloc\_size 8192
query\_response\_time\_range\_base 10
rand\_seed1
rand\_seed2
range\_alloc\_block\_size 4096
read\_buffer\_size 262144
read\_only OFF
read\_rnd\_buffer\_size 4194304
relay\_log /mnt/mysql\_relaylogs/int-btr-db03\_relay\_bin
relay\_log\_index
relay\_log\_info\_file relay-log.info
relay\_log\_purge ON
relay\_log\_space\_limit 10737418240
report\_host int-btr-db03
report\_password
report\_port 3306
report\_user
rpl\_recovery\_rank 0
secure\_auth OFF
secure\_file\_priv
server\_id 12962
skip\_external\_locking ON
skip\_name\_resolve ON
skip\_networking OFF
skip\_show\_database OFF
slave\_compressed\_protocol OFF
slave\_exec\_mode STRICT
slave\_load\_tmpdir /tmp
slave\_net\_timeout 3600
slave\_skip\_errors OFF
slave\_transaction\_retries 10
slow\_launch\_time 2
slow\_query\_log ON
slow\_query\_log\_file /mnt/logs/mysql/mysql\_slow.log
slow\_query\_log\_microseconds\_timestamp OFF
socket /mnt/mysql/mysql.sock
sort\_buffer\_size 4194304
sql\_auto\_is\_null ON
sql\_big\_selects ON
sql\_big\_tables OFF
sql\_buffer\_result OFF
sql\_log\_bin ON
sql\_log\_off OFF
sql\_log\_update ON
sql\_low\_priority\_updates OFF
sql\_max\_join\_size 18446744073709551615
sql\_mode
sql\_notes ON
sql\_quote\_show\_create ON
sql\_safe\_updates OFF
sql\_select\_limit 18446744073709551615
sql\_slave\_skip\_counter
sql\_warnings OFF
ssl\_ca
ssl\_capath
ssl\_cert
ssl\_cipher
ssl\_key
storage\_engine MyISAM
suppress\_log\_warning\_1592 OFF
sync\_binlog 0
sync\_frm ON
system\_time\_zone EST
table\_definition\_cache 256
table\_lock\_wait\_timeout 50
table\_open\_cache 2048
table\_type MyISAM
thread\_cache\_size 300
thread\_handling one-thread-per-connection
thread\_stack 131072
thread\_statistics OFF
time\_format %H:%i:%s
time\_zone SYSTEM
timed\_mutexes OFF
timestamp 1336051628
tmp\_table\_size 33554432
tmpdir /tmp
transaction\_alloc\_block\_size 8192
transaction\_prealloc\_size 4096
tx\_isolation REPEATABLE-READ
unique\_checks ON
updatable\_views\_with\_limit YES
use\_global\_log\_slow\_control none
use\_global\_long\_query\_time OFF
userstat\_running OFF
version 5.1.59-rel13.0-log
version\_comment Percona Server (GPL), 13.0, Revision 325
version\_compile\_machine x86\_64
version\_compile\_os unknown-linux-gnu
wait\_timeout 28800
warning\_count 0
Any help would be greatly appreciated!!!
Thank you
JG
---
### Author:  [@gmouse](https://forums.percona.com/u/gmouse)
#### Post date: [May 3, 2012, 8:55am UTC](https://forums.percona.com/t/percona-mysql-5-1-59-rel13-0-log-performance-issues/1808/2 "2012-05-03T08:55:23Z")
I don’t see any query or SHOW CREATE TABLE output. You seem to think there is a single set of parameters that is best for all queries, but sometimes much more can be gained from rewriting a query.
You could start with [URL][MySQL Tools and Management Software to Perform System Tasks by Percona](https://tools.percona.com/%5B/URL%5D)
---
### Author:  [@JohnG](https://forums.percona.com/u/JohnG)
#### Post date: [May 3, 2012, 9:24am UTC](https://forums.percona.com/t/percona-mysql-5-1-59-rel13-0-log-performance-issues/1808/3 "2012-05-03T09:24:27Z")
I’ve downloaded the Percona ToolKit and read many, many articles to try to tweak the server but obviously, I’m missing something…
It’s a complex query… below is the query followed by some explanations that can help understand.
SELECT
u.uid,
u.email,
SHA1(CONCAT(u.uid, u.email, u.password)) AS `hash`,
ui.country
FROM users u
JOIN users\_info ui ON ui.uid = u.uid
JOIN users\_dates ud ON ud.uid = u.uid
WHERE
u.confirm\_flag = 1 AND
u.status = ‘ACTIVE’ AND
ui.country IN (‘US’, ‘CA’)
AND (
ud.last\_login \>= NOW() - INTERVAL 270 DAY OR
ud.date\_joined \>= NOW() - INTERVAL 45 DAY OR
EXISTS(
SELECT \*
FROM stats.users\_counters
WHERE
uid = u.uid AND
type = 4 AND posted\_on \>= NOW() - INTERVAL 90 DAY) OR
EXISTS(
SELECT \*
FROM order\_headers
WHERE uid = u.uid
)
)
AND (
FIND\_IN\_SET(‘dailyreminders’, ui.preferences\_list)
) AND (
SELECT send\_mail
FROM users\_mailing
WHERE uid = u.uid
ORDER BY posted\_on DESC
LIMIT 1
) = 1
users\_counters table contains 250 million records.
and looks like this:
CREATE TABLE `users_counters` (
`uid` int(10) unsigned NOT NULL,
`type` tinyint(3) unsigned NOT NULL,
`posted_on` datetime NOT NULL,
KEY `uid` (`uid`,`type`,`posted_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
the users table has appros. 7 million records and looks like this:
CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL AUTO\_INCREMENT,
`email` varchar(50) NOT NULL,
`password` varchar(40) NOT NULL,
`confirm_flag` tinyint(3) unsigned NOT NULL DEFAULT ‘0’,
`status` enum(‘ACTIVE’,‘INACTIVE’) NOT NULL DEFAULT ‘ACTIVE’,
PRIMARY KEY (`uid`),
KEY `email_ndx` (`email`(15)),
KEY `idx_confirm_flag_Status_uid` (`confirm_flag`,`status`,`uid`)
) ENGINE=InnoDB AUTO\_INCREMENT=6729960 DEFAULT CHARSET=utf8
/\*!50100 PARTITION BY KEY (uid)
PARTITIONS 10 \*/
the users\_info tale also contains 7 million records
CREATE TABLE `users_info` (
`uid` int(10) unsigned NOT NULL,
`name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`country` char(2) NOT NULL,
`gender` enum(‘Male’,‘Female’) NOT NULL DEFAULT ‘Male’,
`mobile` varchar(15) DEFAULT NULL,
`postal` varchar(15) DEFAULT NULL,
`password_hint` varchar(50) DEFAULT NULL,
`personal_code` varchar(25) NOT NULL,
`invitation_code` varchar(25) DEFAULT NULL,
`reference` varchar(100) DEFAULT NULL,
`cobrand_keyword` varchar(25) DEFAULT NULL,
`preferences_list` varchar(250) DEFAULT NULL,
`class_list` varchar(250) DEFAULT NULL,
`notes` varchar(50) DEFAULT NULL,
`queuestatus` int(1) unsigned DEFAULT NULL,
`update_ns` int(1) unsigned DEFAULT NULL,
`nsid` int(10) unsigned DEFAULT NULL,
`address1` varchar(100) DEFAULT NULL,
`address2` varchar(100) DEFAULT NULL,
`city` varchar(100) DEFAULT NULL,
`region` char(2) DEFAULT NULL,
`birthdate` date DEFAULT NULL,
`register_ipaddr` int(11) DEFAULT NULL,
`register_useragent` varchar(500) DEFAULT NULL,
PRIMARY KEY (`uid`),
KEY `personal_code_ndx` (`personal_code`(12)),
KEY `invitation_code_ndx` (`invitation_code`(12)),
KEY `preferences_list` (`preferences_list`),
KEY `idx_uid_country_prefList` (`uid`,`country`,`preferences_list`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/\*!50100 PARTITION BY KEY (uid)
PARTITIONS 10 \*/
the users\_dates also contain 7 million records
CREATE TABLE `users_dates` (
`uid` int(10) unsigned NOT NULL,
`date_joined` datetime DEFAULT NULL,
`last_login` datetime DEFAULT NULL,
`last_purchase` datetime DEFAULT NULL,
PRIMARY KEY (`uid`),
KEY `idx_uid_joined_login_dates` (`uid`,`date_joined`,`last_login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/\*!50100 PARTITION BY KEY (uid)
PARTITIONS 10 \*/
Order\_header contains 2.2 Million records
CREATE TABLE `order_headers` (
`order_id` int(10) unsigned NOT NULL AUTO\_INCREMENT,
`payment_ref_id` varchar(20) DEFAULT NULL,
`settle_ref_id` varchar(20) DEFAULT NULL,
`refund_ref_id` varchar(20) DEFAULT NULL,
`settle_date` datetime DEFAULT NULL,
`cancel_date` datetime DEFAULT NULL,
`refund_date` datetime DEFAULT NULL,
`refund_reason` text,
`cancel_attempts` tinyint(3) unsigned NOT NULL DEFAULT ‘0’,
`order_type` varchar(20) NOT NULL DEFAULT ‘WEBORDER’,
`uid` int(10) unsigned NOT NULL,
`email` varchar(50) DEFAULT NULL,
`order_status` varchar(20) DEFAULT ‘ACTIVE’,
`order_date` datetime NOT NULL,
`billing_card_type` varchar(2) DEFAULT NULL,
`billing_card_lastdigits` varchar(5) DEFAULT NULL,
`billing_name` varchar(75) DEFAULT NULL,
`billing_address1` varchar(75) DEFAULT NULL,
`billing_address2` varchar(75) DEFAULT NULL,
`billing_city` varchar(50) DEFAULT NULL,
`billing_state` varchar(2) DEFAULT NULL,
`billing_country` varchar(2) DEFAULT NULL,
`billing_postal` varchar(12) DEFAULT NULL,
`billing_phone` varchar(20) DEFAULT NULL,
`shipping_name` varchar(75) DEFAULT NULL,
`shipping_address1` varchar(75) DEFAULT NULL,
`shipping_address2` varchar(75) DEFAULT NULL,
`shipping_city` varchar(50) DEFAULT NULL,
`shipping_state` varchar(2) DEFAULT NULL,
`shipping_country` varchar(2) DEFAULT NULL,
`shipping_postal` varchar(12) DEFAULT NULL,
`shipping_phone` varchar(20) DEFAULT NULL,
`total_order_value_usd` float DEFAULT ‘0’,
`total_shipping_usd` float DEFAULT ‘0’,
`total_tax1_usd` float DEFAULT ‘0’,
`total_tax2_usd` float DEFAULT ‘0’,
`total_order_value_cad` float DEFAULT ‘0’,
`total_shipping_cad` float DEFAULT ‘0’,
`total_tax1_cad` float DEFAULT ‘0’,
`total_tax2_cad` float DEFAULT ‘0’,
`shipping_tax_group` smallint(5) unsigned NOT NULL,
`total_credit_applied` float DEFAULT ‘0’,
`total_discount_usd` float DEFAULT NULL,
`total_discount_cad` float DEFAULT NULL,
`ordered_currency` varchar(3) DEFAULT NULL,
`ordered_currency_rate` float DEFAULT ‘1’,
`order_notes` varchar(100) DEFAULT NULL,
`shipping_date` datetime DEFAULT NULL,
`tracking_number` varchar(50) DEFAULT NULL,
`reference_date` datetime DEFAULT NULL,
`reference` varchar(100) DEFAULT NULL,
`recipient_email` varchar(50) DEFAULT NULL,
`gift_flag` int(1) unsigned DEFAULT NULL,
`credit_total_referral` decimal(12,2) DEFAULT ‘0.00’,
`credit_total_transition` decimal(12,2) DEFAULT ‘0.00’,
`credit_total` decimal(12,2) DEFAULT ‘0.00’,
`credit_total_promo` decimal(12,2) DEFAULT ‘0.00’,
`credit_total_preacquisition` decimal(12,2) DEFAULT ‘0.00’,
`queuestatus` int(1) unsigned DEFAULT NULL,
`update_ns` int(1) unsigned DEFAULT NULL,
`nsid` int(10) unsigned DEFAULT NULL,
`merchant_ref_id` bigint(20) unsigned DEFAULT NULL,
`code_avs` char(1) DEFAULT NULL,
`code_cvd` char(1) DEFAULT NULL,
`ip_address` int(10) unsigned DEFAULT NULL,
`avs_passed` tinyint(1) unsigned DEFAULT NULL,
`avs_message` varchar(255) DEFAULT NULL,
`user_agent` varchar(500) DEFAULT NULL,
PRIMARY KEY (`order_id`,`order_type`),
KEY `order_id` (`order_id`,`uid`),
KEY `order_type` (`uid`,`order_type`),
KEY `cancel_attempts` (`cancel_attempts`) USING BTREE,
KEY `order_date` (`order_date`) USING BTREE,
KEY `payment_ref_id` (`payment_ref_id`) USING BTREE,
KEY `refund_ref_id` (`refund_ref_id`) USING BTREE,
KEY `refund_date` (`refund_date`) USING BTREE,
KEY `settle_ref_id` (`settle_ref_id`) USING BTREE,
KEY `settle_date` (`settle_date`) USING BTREE
) ENGINE=InnoDB AUTO\_INCREMENT=10390814 DEFAULT CHARSET=utf8
users\_mailing has 12 million records
CREATE TABLE `users_mailing` (
`uid` int(10) unsigned NOT NULL,
`posted_on` datetime NOT NULL,
`send_mail` tinyint(3) unsigned NOT NULL DEFAULT ‘1’,
`update_source` tinyint(3) unsigned NOT NULL DEFAULT ‘0’,
KEY `uid` (`uid`,`posted_on`),
KEY `idx_uid_posted_sendMail` (`uid`,`posted_on`,`send_mail`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/\*!50100 PARTITION BY KEY (uid)
PARTITIONS 10 \*/
the EXPLAIN looks like this…
1 PRIMARY u ref PRIMARY,idx\_confirm\_flag\_Status\_uid idx\_confirm\_flag\_Status\_uid 2 const,const 3366992 Using where
1 PRIMARY ui ref idx\_uid\_country\_prefList idx\_uid\_country\_prefList 4 1965917\_btr.u.uid 1 Using where; Using index
1 PRIMARY ud ref idx\_uid\_joined\_login\_dates idx\_uid\_joined\_login\_dates 4 1965917\_btr.ui.uid 1 Using where; Using index
4 DEPENDENT SUBQUERY users\_mailing ref uid,idx\_uid\_posted\_sendMail uid 4 1965917\_btr.u.uid 1 Using where; Using filesort
3 DEPENDENT SUBQUERY order\_headers ref order\_type order\_type 4 1965917\_btr.u.uid 1 Using index
2 DEPENDENT SUBQUERY users\_counters ref uid uid 5 1965917\_btr.u.uid,const 12 Using where; Using index
What I’ve tried with the original query is the following…
Straight\_joins
added indexes
used the use index hint
used left joins instead of sub queries
broke down the query using tekporary tables with indexes
Now I’m working on pre summarizing the big tables on a nightly basis, although I’m not sure if it will be accepted (if it is time sensitive material)
That’s it… any help would be greatly appreciated.
Thank you
JG