Hey,
More learning about inner workings of the database engine and performance
tuning. I've seen some behavior I can't understand when studying EXPLAIN
output.
Attached is an ASCII text file SQL1.txt has the CREATE TABLE, INSERT, CREATE
INDEXES required for this sample. After the two tables are populated, I
issue the following using SQL Studio:
EXPLAIN
SELECT FA1, FA2, FA3, FAPayload1, FAPayload2, FAPayload3, FB1, FBPayload1
FROM TestA, TestB
WHERE FA3=10
AND FA4=1
AND TestA.FA2=TestB.FB1
My Questions:
1. Explain output shows 154 PAGECOUNT for the Join. Are these 8KB MaxDB
storage pages? Does that mean 1232 kilobytes of Disk I/O?
2. Is there a better (performing) way to write such a join? Union?
Subselect?
3. The SQL1.txt setup creates 6 indexes. For the EXPLAIN output, it shows
that TESTAINDEX5 is being used. Would it not make more sense for the MaxDB
engine to use TESTAINDEX6 - isn't that query an exact match with the WHERE
clause? Isn't this causing more I/O than needed using a INDEX that has a
unused field (FAPayload3 is on TESTAINDEX5 - but is not used in the WHERE).
In our production application. TableA has about 4 million records and
TableB has about 3.5 million records. The indexes and keys illustrated are
the same type for our most frequent query. We know we can denormalize the
two tables - but we also want to understand why there is so much disk I/O on
this query - and why we do not understand the selection of INDEX.
Thank you.
Stephen Gutknecht
Full time traveler... Currently in Lake Havasu City, Arizona USA
CREATE TABLE TestA (FA1 INTEGER PRIMARY KEY NOT NULL, FA2 INTEGER NOT NULL, FA3
INTEGER NOT NULL, FA4 SMALLINT, FAPayload1 VARCHAR(16), FAPayload2 VARCHAR(128),
FAPayload3 TIMESTAMP)
CREATE TABLE TestB (FB1 INTEGER PRIMARY KEY NOT NULL, FBPayload1 VARCHAR(128),
FBPayload2 TIMESTAMP)
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1000, 2000, 10, 1, 'Record 1000', 'Record 1000 Extra', {ts '2003-11-26
01:00:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1001, 2001, 10, 1, 'Record 1001', 'Record 1001 Extra', {ts '2003-11-26
01:01:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1002, 2002, 10, 2, 'Record 1002', 'Record 1002 Extra', {ts '2003-11-26
01:02:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1003, 2003, 10, 1, 'Record 1003', 'Record 1003 Extra', {ts '2003-11-26
01:03:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1004, 2004, 10, 1, 'Record 1004', 'Record 1004 Extra', {ts '2003-11-26
01:03:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1005, 2005, 10, 1, 'Record 1005', 'Record 1005 Extra', {ts '2003-11-26
01:04:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1006, 2006, 10, 1, 'Record 1006', 'Record 1006 Extra', {ts '2003-11-26
01:05:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1007, 2007, 10, 1, 'Record 1007', 'Record 1007 Extra', {ts '2003-11-26
01:06:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1008, 2008, 10, 1, 'Record 1008', 'Record 1008 Extra', {ts '2003-11-26
01:07:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1009, 2009, 10, 1, 'Record 1009', 'Record 1009 Extra', {ts '2003-11-26
01:08:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1010, 2100, 10, 1, 'Record 1010', 'Record 1010 Extra', {ts '2003-11-26
01:09:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1011, 2100, 10, 1, 'Record 1011', 'Record 1011 Extra', {ts '2003-11-26
01:10:00.0000'})
INSERT INTO TESTA (FA1, FA2, FA3, FA4, FAPayload1, FAPayload2, FAPayload3) VALUES
(1012, 2100, 10, 1, 'Record 1012', 'Record 1012 Extra', {ts '2003-11-26
01:11:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2000, 'Reference 2000', {ts
'2003-11-26 02:00:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2001, 'Reference 2001', {ts
'2003-11-26 02:01:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2002, 'Reference 2002', {ts
'2003-11-26 02:02:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2003, 'Reference 2003', {ts
'2003-11-26 02:03:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2004, 'Reference 2004', {ts
'2003-11-26 02:04:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2005, 'Reference 2005', {ts
'2003-11-26 02:05:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2006, 'Reference 2006', {ts
'2003-11-26 02:06:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2007, 'Reference 2007', {ts
'2003-11-26 02:07:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2008, 'Reference 2008', {ts
'2003-11-26 02:08:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2009, 'Reference 2009', {ts
'2003-11-26 02:09:00.0000'})
INSERT INTO TESTB (FB1, FBPayload1, FBPayload2) VALUES (2100, 'Reference 2100', {ts
'2003-11-26 02:10:00.0000'})
CREATE INDEX TestAIndex1 ON TestA (FA3, FA2, FAPayload3)
CREATE INDEX TestAIndex2 ON TestA (FA3, FA2)
CREATE INDEX TestAIndex3 ON TestA (FA3)
CREATE INDEX TestAIndex4 ON TestA (FA2)
CREATE INDEX TestAIndex5 ON TestA (FA3, FA4, FAPayload3)
CREATE INDEX TestAIndex6 ON TestA (FA3, FA4, FA2)
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
