slow query

MySQL & MariaDB Other MySQL® Questions
1

hello everyone
I am new here and I come with a question relating to relatively bigger tables: we are using MyISAM database with may tables including the two used in the query below.
Table phptbv_calls contain during tests some 10 million records with call data for different tel extensions
Table phptbv_vport contain some 3000 tel extensions detail records

The query looks following:

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

index primary is on calls_date + calls_dialled + calls_vport_id

when I use MySQLQueryBrowser the query returns 30 rows in time shown as 0,3031s (9,9303s)

  1. how should I understand the two times in the result?
  2. is there a way to shorten the time for a query?

looking for some help :slight_smile:

2

Honestly I can’t tell you what these times are as I do not use Query Browser and other tools like command line client will give only one time.

But you should see yourself if query actually takes 0.3 sec or 9 secs it is hard to be mistaken.

Regarding your query - it would be good if you post explain for it.
It is hard to tell anything looking at query alone.

3

Peter

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.

The situation:
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

The machine:
desktop, 1GHz, 256MB ram

The query:
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…