note: this is this message's second sending after some snippage to meet
the 3 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
