thanks for your message
of course I have noticed that the query time took 9 sec to produce results yet still I do not understand what does the other time mean. I noticed that while trying different queries it usually is smaller but in a few cases it was actually bigger - much to my suprise.
there are two tables:
Table phptbv_calls contain call detail information like
CALLS_DATE - date when the call was made
CALLS_LOCATION_NAME - name of the city sb was calling to
CALLS_DIALLED - dialled number
this table has about 10million rows each about 70bytes long
Table phptbv_vport contain extension details
VPORT_NR - extension number
VPORT_INFO - extension type
this table has about 3000rows each about 60 bytes long
desktop, 1GHz, 256MB ram
the query is expected to produce listing of several fields describing calls done from selected extension (here: extension number 2345, although there may be more extensions)
during selected period of time (here 2006-12-01)
sorted by date, extension number and call duration.
of course the sorting make by extension makes sense only if calls are listed not for one but for more extensions.
When I asked for explain I got following results:
Query explain SELECT CALLS_DATE, CALLS_LOCATION_NAME, CALLS_DIALLED, CALLS_VTRUNK_NAME, CALLS_SPECIAL, CALLS_SPECIALINFO, CALLS_DURATION, CALLS_PULSES, CALLS_COST, CALLS_COST2, VPORT_NR, VPORT_INFO, CALLS_SPECIAL_NR FROM phpTBV_CALLS, phpTBV_VPORT where CALLS_VPORT_ID = VPORT_NEWID and CALLS_DATE > ‘2006.12.01’ and CALLS_DATE <= ‘2006.12.02’ and VPORT_NR in (2345) order by CALLS_DATE , VPORT_NR, CALLS_DURATION,
Sat Jan 27 00:42:23 2007
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | phpTBV_CALLS | range | I_I_PK_phpTBV_CALLS,I_I_IX_phpTBV_CALLS_1,I_I_IX_phpTBV_CALL S_11,I_I_IX_phpTBV_CALLS_2,I_I_IX_phpTBV_CALLS_3,I_I_IX_phpT BV_CALLS_4,I_DATE_VPORT | I_I_PK_phpTBV_CALLS | 8 | null | 8355 | Using where
1 | SIMPLE | phpTBV_VPORT | eq_ref | PRIMARY,I_PK_phpTBV_VPORT,I_IX_phpTBV_VPORT_1,I_IX_phpTBV_VP ORT_2,I_IX_phpTBV_VPORT_3 | PRIMARY | 4 | test.phpTBV_CALLS.CALLS_VPORT_ID | 1 | Using where
hope this description is clear enough - if not pls advise what info is needed - my experience here is not too big…