如何使用Union优化Mysql Select Query？

i am working on mysql database with InnoDb storage engine. my table structure are as follows:

Table Name : archiveincomingsms

Index Detail of : archiveincomingsms

Table Name : archiveoutgoingsms

Index Detail of : archiveoutgoingsms

Above are my table structure and their index detail !

Both table have minimum more then 1 billion records(rows) respectively.

Now Problem is that when i would like to execute following SqlQuery :

      ( SELECT  id AS ID, `recieved_datetime` `Date`, 'MT' AS Type, src_adress AS Msisdn,
               TEXT as text, CHAR_LENGTH(TEXT) AS QtyOfSymbols, 'OK' AS `Status`
            FROM  archiveincomingsms
            WHERE  1=1
              AND  recieved_datetime BETWEEN '2015-06-14 00:00:00'
                                         AND '2015-07-14 23:59:59'
      )
    UNION  ALL
      ( SELECT  id AS ID, `send_date` `Date`, 'MO' AS Type, scr_adress AS Msisdn,
                TEXT as text, CHAR_LENGTH(TEXT) AS QtyOfSymbols, 'OK' AS `Status`
            FROM  archiveoutgoingsms
            WHERE  1=1
              AND  send_date BETWEEN '2015-06-14 00:00:00'
                                 AND '2015-07-14 23:59:59'
      )
    ORDER BY  `Date` ASC
    LIMIT  0 ,100 

Above query takes more than 30 second time to get data from tables. and Another one is i have to count number of row too for pagination purpose and it also take more then 30 second time.

Overall it takes more than 1 minute to execute. Is there any proper way to optimize time ? i have to take down into maximum 5 sec. ! How can it possible ? I am using mysql database !

I cann't see indexes.

Please, result of query:

EXPLAIN (SELECT id AS ID,`recieved_datetime` `Date`,'MT' AS Type,src_adress AS Msisdn,TEXT as text,CHAR_LENGTH(TEXT) AS QtyOfSymbols,'OK' AS `Status` FROM archiveincomingsms
                  WHERE 1=1 AND recieved_datetime BETWEEN '2015-06-14 00:00:00' AND '2015-07-14 23:59:59')
                  UNION ALL
                  (SELECT id AS ID,`send_date` `Date`,'MO' AS Type,scr_adress AS Msisdn,TEXT as text,CHAR_LENGTH(TEXT) AS QtyOfSymbols,'OK' AS `Status` FROM archiveoutgoingsms
                  WHERE 1=1 AND send_date BETWEEN '2015-06-14 00:00:00' AND '2015-07-14 23:59:59') ORDER BY `Date` ASC LIMIT 0 ,100

What is when use

ORDER BY `id` ASC

insted

ORDER BY `Date` ASC

?

Here is the result of this Query Explain !

Image can view by open in new tab ! it's too small here !

Somehow mysql don't use index recieved_datetime in first part of UNION

You can tell MYSQL force use of index

Query 1:

EXPLAIN (SELECT id AS ID,`recieved_datetime` `Date`,'MT' AS Type,src_adress AS Msisdn,TEXT as text,CHAR_LENGTH(TEXT) AS QtyOfSymbols,'OK' AS `Status` FROM archiveincomingsms FORCE INDEX(recieved_datetime)
              WHERE 1=1 AND recieved_datetime BETWEEN '2015-06-14 00:00:00' AND '2015-07-14 23:59:59')
              UNION ALL
              (SELECT id AS ID,`send_date` `Date`,'MO' AS Type,scr_adress AS Msisdn,TEXT as text,CHAR_LENGTH(TEXT) AS QtyOfSymbols,'OK' AS `Status` FROM archiveoutgoingsms
              WHERE 1=1 AND send_date BETWEEN '2015-06-14 00:00:00' AND '2015-07-14 23:59:59') ORDER BY `Date` ASC LIMIT 0 ,100

Query 2:

(SELECT id AS ID,`recieved_datetime` `Date`,'MT' AS Type,src_adress AS Msisdn,TEXT as text,CHAR_LENGTH(TEXT) AS QtyOfSymbols,'OK' AS `Status` FROM archiveincomingsms FORCE INDEX(recieved_datetime)
              WHERE 1=1 AND recieved_datetime BETWEEN '2015-06-14 00:00:00' AND '2015-07-14 23:59:59')
              UNION ALL
              (SELECT id AS ID,`send_date` `Date`,'MO' AS Type,scr_adress AS Msisdn,TEXT as text,CHAR_LENGTH(TEXT) AS QtyOfSymbols,'OK' AS `Status` FROM archiveoutgoingsms
              WHERE 1=1 AND send_date BETWEEN '2015-06-14 00:00:00' AND '2015-07-14 23:59:59') ORDER BY `Date` ASC LIMIT 0 ,100

What about to use timestamp instead datetime 2015-06-14 00:00:00

Please provide SHOW CREATE TABLE archiveincomingsms; -- it is clearer and more readable that what you provided. Plus it shows the Engine and Index(es).

For the UNION, you need

INDEX(send_date),
INDEX(received_date)

However, since you are looking at a month's worth of data, that could be a significant chunk of the table. If it is more than something like 20% of the table, it will do a table scan (or two scans), meaning that the UNION does not help.

A side note: Instead of

          AND  send_date BETWEEN '2015-06-14 00:00:00'
                             AND '2015-07-14 23:59:59'

This would be cleaner and easier:

          AND  send_date >= '2015-06-14'
          AND  send_date <  '2015-06-14' + INTERVAL 1 MONTH

and it automatically handles short months and year boundaries.

The tablenames are hard to distinguish; they all look like

archivemiscellanyms 

Oh, I am not finished (now that I stare at the table names to see that they are different)...

Add ORDER BY...LIMIT to each part of the UNION:

( SELECT ... ORDER BY received_datetime LIMIT    100 )
UNION ALL
( SELECT ... ORDER BY send_date         LIMIT    100 )
ORDER BY date                           LIMIT 0, 100

If you are going to "paginate" (cringe), then do this for, say, the 3rd page:

( SELECT ... ORDER BY received_datetime LIMIT      300 )
UNION ALL
( SELECT ... ORDER BY send_date         LIMIT      300 )
ORDER BY date                           LIMIT 200, 100

Now the indexes will be usable. However, as the user goes from page to page, it will get slower and slower.

You could probably speed up the subsequent pages by "remembering where you left off". See my pagination blog;