My SQL Cluster Queries Optmization

[Ridd1ck]

I have one mysql cluster with 2 data nodes and 2 api nodes,
I have a web application that use big select statement like this:


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


and is really slow with cluster,
can you help me to optimize this Query?
Thanks.

[Wagner Bianchi]

Here we go with some basic hints:

   1. Have you enabled Engine Condition
Pushdown<http://dev.mysql.com/doc/refman/5.1/en/condition-pushdown-optimization.html>?
   (
   http://dev.mysql.com/doc/refman/5.1/en/condition-pushdown-optimization.html
   )
   2. Have you ran that query using EXPLAIN?

Best wishes,
--
*Wagner Bianchi*


2011/11/28 Ridd1ck <[hidden email]>

> I have one mysql cluster with 2 data nodes and 2 api nodes,
> I have a web application that use big select statement like this:
> 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
>
>
> and is really slow with cluster,
> can you help me to optimize thsi Query?
> Thanks.
>
> --
> View this message in context:
> http://mysql-cluster.1050023.n4.nabble.com/My-SQL-Cluster-Queries-Optmization-tp4115737p4115737.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
>
>

[Ridd1ck]

No I will try this.
thank you!

[Johan Andersson-3]

Hi,
engine-condition-pushdown is enabled by default
Do:

explain select..

send the explain + the table defs.


-j

On 2011-11-28 17.49, Ridd1ck wrote:
> No I will try this.
> thank you!
>
> --
> View this message in context: http://mysql-cluster.1050023.n4.nabble.com/My-SQL-Cluster-Queries-Optmization-tp4115737p4115877.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

[Wagner Bianchi]

Hmmm, good touch, Johan...tks.

Best wishes,
--
*Wagner Bianchi*



2011/11/28 Johan Andersson <[hidden email]>

> Hi,
> engine-condition-pushdown is enabled by default
> Do:
>
> explain select..
>
> send the explain + the table defs.
>
>
> -j
>
>
> On 2011-11-28 17.49, Ridd1ck wrote:
>
>> No I will try this.
>> thank you!
>>
>> --
>> View this message in context: http://mysql-cluster.1050023.**
>> n4.nabble.com/My-SQL-Cluster-**Queries-Optmization-**
>> tp4115737p4115877.html<http://mysql-cluster.1050023.n4.nabble.com/My-SQL-Cluster-Queries-Optmization-tp4115737p4115877.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
>
>

[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)


 explain SELECT  session_esme,
    -> msg_smsc_id,
    -> state,
    -> msg_meta_data,
    -> sum(msg_binfo) as sum,
    -> count(*) as num,
    -> year(from_unixtime(msg_timestamp)) as y,
    -> month(from_unixtime(msg_timestamp)) as m,
    -> day(from_unixtime(msg_timestamp)) as d,
    -> hour(from_unixtime(msg_timestamp)) as h
    -> FROM  amd_smpp_gw.MT
    -> where month(from_unixtime(msg_timestamp))=11
    -> and year(from_unixtime(msg_timestamp))=2011
    -> and day(from_unixtime(msg_timestamp))=28
    -> group by
    -> session_esme,
    -> y,m,d,h,
    -> msg_meta_data,msg_smsc_id,state;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | MT    | ALL  | NULL          | NULL | NULL    | NULL | 729282 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

explain select * from amd_smpp_gw.MT where
    ->             msg_timestamp >= (unix_timestamp(NOW())-1600)
    ->             and (updated_billing is null);
+----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+-----------------------------------+
| id | select_type | table | type | possible_keys                 | key             | key_len | ref   | rows | Extra                             |
+----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+-----------------------------------+
|  1 | SIMPLE      | MT    | ref  | msg_timestamp,updated_billing | updated_billing | 2       | const |    9 | Using where with pushed condition |
+----+-------------+-------+------+-------------------------------+-----------------+---------+-------+------+-----------------------------------+
1 row in set (0.00 sec)


explain SELECT msg_id AS num  FROM MT as mt LEFT JOIN http_MT as http ON http.MT_ID=mt.msg_id
    -> WHERE (mt.msg_timestamp BETWEEN '1322431200' AND '1322491463' ) AND   mt.smpp_destination_addr LIKE '%' AND mt.smpp_source_add LIKE '%'
    -> AND mt.msg_id LIKE '%' GROUP BY mt.msg_id;
+----+-------------+-------+--------+---------------+---------------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key           | key_len | ref  | rows  | Extra                                        |
+----+-------------+-------+--------+---------------+---------------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | mt    | range  | msg_timestamp | msg_timestamp | 4       | NULL | 36564 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | http  | eq_ref | PRIMARY       | PRIMARY       | 114     | func |     1 |                                              |
+----+-------------+-------+--------+---------------+---------------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

This Queries is the slowest and is really usefull for my application.
I need help on how can i do this faster because is critical.
Do you need the structure of the tables?

Thanks.

[Wagner Bianchi]

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 <[hidden email]>

>  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
>
>

[Ridd1ck]

I think this is not possible,
anything else?