From: Wagner Bianchi Date: November 29 2011 11:45am Subject: Re: My SQL Cluster Queries Optmization List-Archive: http://lists.mysql.com/cluster/8219 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=20cf305639e9e3e54804b2de2654 --20cf305639e9e3e54804b2de2654 Content-Type: text/plain; charset=ISO-8859-1 Could you think a better strategy for this query? Even having a good explain result, this query will perform a small number of comparisons - 4 x 114. Check if if you can improve the strategy applied on WHERE clauses where the query relies on field = '' and field LIKE '%' ... Best wishes, -- *Wagner Bianchi* 2011/11/29 Ridd1ck > explain SELECT mt.smpp_source_add, mt.msg_id, mt.msg_smsc_id, > mt.default_smsc, mt.msg_smsc_id, mt.ported_smsc1, mt.ported_smsc2, > mt.smpp_destination_addr, mt.state,mt.session_esme, mt.hlr_location, > mt.hlr_state, mt.msg_timestamp, mt.state_timestamp, mt.msg_meta_data, > mt.pb_id, mt.w2sms_client, http.ClientID as http_client FROM MT as mt > LEFT JOIN http_MT as http ON http.MT_ID=mt.msg_id WHERE (mt.msg_timestamp > BETWEEN '1322085600' AND '1322171999' ) AND (mt.session_esme='-1' OR > mt.w2sms_client='') AND mt.smpp_destination_addr LIKE '%' AND > mt.smpp_source_add LIKE '%' AND mt.msg_id LIKE '%' ORDER BY > mt.msg_timestamp > DESC; > > > +----+-------------+-------+--------+-----------------------------------------+---------------+---------+------+-------+-------------+ > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra | > > +----+-------------+-------+--------+-----------------------------------------+---------------+---------+------+-------+-------------+ > | 1 | SIMPLE | mt | range | > session_esme,msg_timestamp,w2sms_client | msg_timestamp | 4 | NULL | > 51288 | Using where | > | 1 | SIMPLE | http | eq_ref | PRIMARY > | PRIMARY | 114 | func | 1 | | > > +----+-------------+-------+--------+-----------------------------------------+---------------+---------+------+-------+-------------+ > 2 rows in set (0.00 sec) > > -- > View this message in context: > http://mysql-cluster.1050023.n4.nabble.com/My-SQL-Cluster-Queries-Optmization-tp4115737p4118348.html > Sent from the MySQL - Cluster mailing list archive at Nabble.com. > > -- > MySQL Cluster Mailing List > For list archives: http://lists.mysql.com/cluster > To unsubscribe: http://lists.mysql.com/cluster > > --20cf305639e9e3e54804b2de2654--