<note: this is this message's second sending after some snippage to meet
the 30000 character limit for the list and other mild editing.-- SG>
This is the query you would like to optimize:
select Sum(m0),Avg(m1),Max(m5),Min(m6) from
ind_kar_bng_robocop_bsc_0_pm_ipc_0 where
bsc_id = 0 and bts_id = 255 and data_Gentime
between "2004-11-22 00:00:00" and
"2004-12-10 19:41:44" and item_id = 0;
If you frequently query on bsc_id, bts_id, item_id, and data_Gentime
(which may be a reasonable combination of values to frequently search by)
you can usually improve query responses by creating a compound index that
contains all of those columns because MySQL does not *yet* combine
single-column indexes to improve performance (it's a 5.2+ optimization and
is still in the works)
ALTER TABLE ind_kar_bng_robocop_bsc_0_pm_ipc_0 ADD KEY(data_Gentime,
item_id, bts_id, bsc_id)
That index should also improve any using any of these combinations of
fields in the WHERE statement:
date_Gentime (alone)
data_Gentime, item_id
data_Gentime, item_id, bts_id
But that index could not assist for queries only looking for:
item_id, bts_id, bsc_id
bts_id, bsc_id
bsc_id
data_Gentime, bts_id, bsc_id
data_Gentime, bsc_id
data_Gentime, bts_id
That is because the index can only assist to locate values where you query
the index from left to right. If you skip an indexed column then a
compound index may become unuseful. If your query patterns are slightly
different, you may want to try a different order to the columns or to try
creating multiple compound indexes, each one with a slightly different
column composition and order.
Additional reading (also available in other languages!):
http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
http://dev.mysql.com/doc/mysql/en/SELECT_speed.html
http://dev.mysql.com/doc/mysql/en/Where_optimizations.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
chetan t <[EMAIL PROTECTED]> wrote on 12/07/2004 12:07:39 AM:
> Hello,
>
>
> I am a software developer,
> I am using Mysql-4.1.3b-beta-nt,
> ODBC3.5.1 driver on windows PC for an
> application development.
>
> the application which i am developing is a
> client-server architecture based,in which we have to
> store data of the BSM(Base Station Manager of CDMA
> network).
> the nature of data is records containing alarms and
> faults
> occuring in the CDMA system,after storing the data we
> need to generate statistical reports on these data
>
> my table structure is as fallows,
>
> CREATE TABLE ind_kar_bng_robocop_bsc_0_pm_ipc_0
> (
> gan_id INTEGER NOT NULL,
> bsc_id INTEGER NOT NULL,
> bts_id INTEGER NOT NULL,
> bd_type VARCHAR(10) NOT NULL,
> bd_id INTEGER NOT NULL,
> duplex VARCHAR(10) NOT NULL,
> data_GenTime DATETIME NOT NULL,
> item_id INTEGER NOT NULL,
> M0 INTEGER NOT NULL,
> M1 INTEGER NOT NULL,
> M2 INTEGER NOT NULL,
> M3 INTEGER NOT NULL,
> M4 INTEGER NOT NULL,
> M5 INTEGER NOT NULL,
> M6 INTEGER NOT NULL,
> M7 INTEGER NOT NULL,
> M8 INTEGER NOT NULL,
> M9 INTEGER NOT NULL,
> M10 INTEGER NOT NULL,
> M11 INTEGER NOT NULL,
> M12 INTEGER NOT NULL,
> M13 INTEGER NOT NULL,
> M14 INTEGER NOT NULL,
> M15 INTEGER NOT NULL,
> M16 INTEGER NOT NULL,
> M17 INTEGER NOT NULL,
> M18 INTEGER NOT NULL,
> M19 INTEGER NOT NULL,
> M20 INTEGER NOT NULL,
> M21 INTEGER NOT NULL,
> M22 INTEGER NOT NULL,
> M23 INTEGER NOT NULL,
> M24 INTEGER NOT NULL,
> M25 INTEGER NOT NULL,
> M26 INTEGER NOT NULL,
> M27 INTEGER NOT NULL,
> M28 INTEGER NOT NULL,
> M29 INTEGER NOT NULL,
> M30 INTEGER NOT NULL,
> M31 INTEGER NOT NULL,
> KEY DateIndex (data_GenTime),
> KEY gan_idIndex (gan_id) ,
> KEY bsc_idIndex (bsc_id) ,
> KEY bts_idIndex (bts_id) ,
> KEY bd_typeIndex (bd_type) ,
> KEY bd_idIndex (bd_id) ,
> KEY item_idIndex (item_id));
>
>
> the type of query that is executed is as below
>
>
> mysql> select Sum(m0),Avg(m1),Max(m5),Min(m6) from
> ind_kar_bng_robocop_bsc_0_pm_ipc_0 where
> bsc_id = 0 and bts_id = 255 and data_Gentime
> between "2004-11-22 00:00:00" and
> "2004-12-10 19:41:44" and item_id = 0;
> +----------+-----------+---------+---------+
> | Sum(m0) | Avg(m1) | Max(m5) | Min(m6) |
> +----------+-----------+---------+---------+
> | 23376896 | 1154.9079 | 0 | 0 |
> +----------+-----------+---------+---------+
>
> 1 row in set (3 min 30.35 sec)
>
>
> this query executed when the record count in the table
> ind_kar_bng_robocop_bsc_0_pm_ipc_0 was 79,21,988
> records
>
> mysql> select count(*) from
> ind_kar_bng_robocop_bsc_0_pm_ipc_0;
> +----------+
> | count(*) |
> +----------+
> | 7921988 |
> +----------+
>
> as the number of record in the table keep on growing
> the
> query response time increases..
>
> the explain select result of the same query is here
>
> mysql> explain select Sum(m0),Avg(m1),Max(m5),Min(m6)
> from ind_kar_bng_robocop_bsc_0_pm_ipc_0 where
> bsc_id = 0 and bts_id = 255 and data_Gentime
> between "2004-11-22 00:00:00" and
> "2004-12-10 19:41:44" and item_id = 0 \G
> *************************** 1. row
> ***************************
> id: 1
> select_type: SIMPLE
> table: ind_kar_bng_robocop_bsc_0_pm_ipc_0
> type: ref
> possible_keys:
> DateIndex,bsc_idIndex,bts_idIndex,item_idIndex
> key: item_idIndex
> key_len: 4
> ref: const
> rows: 820535
> Extra: Using where
> 1 row in set (0.28 sec)
>
<<<<huge snip>>>
> Please sujjest a way to reduce query response time to
> few seconds
> either by altering system variables or by any other
> method possible
>
> thanks in advance,
> waiting for the reply,
> Chethan.
>
>
>
> __________________________________
> Do you Yahoo!?
> Meet the all-new My Yahoo! - Try it today!
> http://my.yahoo.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
