How to speed up performance in MySQL

Question

I have used this query to show the results that I want.

It works perfectly, but I have one problem. When the data in the database have thousands or records it becomes slower. I have heard about data massage.

If I massage the data can it perform better?

<?php
public function search($searchterm)
{
    $sql = "
        SELECT 
            CAST(t.date as Date) AS Date, 
            SUM(t.Transaction)+SUM(r.Request) AS allTransaction, 
            SUM(t.Success)+SUM(r.RequestSuccess) AS allSuccess, 
            t.Transaction, 
            t.Success, 
            r.Request, 
            r.RequestSuccess 
        FROM 
            (
            SELECT 
                date, 
                COUNT(DISTINCT no_a) AS Transaction,            
                SUM(t.status = 0) AS Success
            FROM 
                transfer_tx_201503 AS t 
            WHERE 
                CAST(t.date as time) BETWEEN '00:00:00' AND '$searchterm' 
            GROUP BY CAST(date as Date) DESC
            ) AS t 

            JOIN

            (
            SELECT 
                date, 
                COUNT(DISTINCT no_a) AS Request, 
                SUM(r.status = 0) AS RequestSuccess
            FROM 
                request_tx_201503 AS r 
            WHERE 
                CAST(r.date as time) BETWEEN '00:00:00' AND '$searchterm' 
            GROUP BY CAST(date as Date) DESC
            ) AS r

            ON 
                CAST(t.date as date) = CAST(r.date as date)
            GROUP BY 
                Date 
            DESC";

    $q = $this->db->query($sql);
    if($q->num_rows() > 0)
    {
        foreach($q->result() as $row)
        {
            $data[] = $row;
        }

        return $data;
    }
}

if you could format that query better, so it doesn't need horizontal scroll, that'd be great. also, the results of show create table on each table, and the results of the query explain plan would be helpful — pala_, Apr 02 '15 at 02:03
Why re you casting the date column as a Date|? Surely it a DATE or DATETIME column to begin with. Also date is a reserved word and shou;ld not be used for a column name. The fact that it still works is for backward compatibility reasons does not make it right. There are after all enough words in any language not to have to use a reserved word. Desides what date is it? Date of Birth, created date, booking date, or what. Use a name that means somethng. — David Soussan, Apr 02 '15 at 02:12
The you don't have to use cast which is slowing things down. Use one of the date functions if you just want the date or time portion. — David Soussan, Apr 02 '15 at 02:17
the problem is my company use datetime datatype, they can run more faster than mine. but they split the query. is that possible and how ? btw, im doing internship. and still a lot to learn — Syahmi Roslan, Apr 02 '15 at 02:21

score 1 · Accepted Answer · answered Apr 02 '15 at 02:14

First, you only need to cast the date once rather than in the inner an outer queries.

Second, you don't need to re-aggregate the data in the outer query.

SELECT t.date as Date,
       (t.Transaction + r.Request) as allTransaction,  
       (t.Success + r.RequestSuccess) as allSuccess,
       t.Transaction, t.Success, r.Request, r.RequestSuccess 
FROM (select CAST(date as Date) as date, count(DISTINCT no_a) as Transaction,
             sum(t.status = 0) as Success
      from transfer_tx_201503 t
      WHERE CAST(t.date as time) BETWEEN '00:00:00' AND '$searchterm'
      group by CAST(date as Date) desc
     ) t JOIN
     (select CAST(date as Date) as date, count(DISTINCT no_a) as Request,
             SUM(r.status = 0) as RequestSuccess
      from request_tx_201503 r
      where CAST(r.date as time) BETWEEN '00:00:00' AND '$searchterm' 
      group by CAST(date as Date) desc
     ) r
     ON t.date = r.date
GROUP BY Date desc;

Unfortunately, there is not an obvious way to add indexes to help this query. If you split the date and time into two columns in each of the tables, then indexes could help the query.

is that the only way to improve the perfomance ? by split the column date and time — Syahmi Roslan, Apr 02 '15 at 02:30
@SyahmiRoslan . . . Given the condition you have on the time component and the aggregation by the date, that all I can think of. — Gordon Linoff, Apr 02 '15 at 02:36

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

Looks like you need to add some indexes on your mysql database tables, likely on your date columns(s). I would need to see the schema from the database tables to help any more. Also, the results of 'explain' on the query would be helpful as pala_ suggested.

https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

How do I add indices to MySQL tables?

edited May 23 '17 at 10:27

Community

1
1

answered Apr 02 '15 at 02:14

Allan Nienhuis

3,961
2
23
19

How to speed up performance in MySQL

2 Answers2