Announcement

Announcement Module
Collapse
No announcement yet.

slow query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • slow query

    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

  • #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.

    Comment


    • #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_p hpTBV_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...

      Comment

      Working...
      X