| slow query [message #641] |
Thu, 25 January 2007 16:59  |
 wojtekszala
Messages: 2
Registered: January 2007
Location: poland |
Junior Member |
|
|
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 :)
wojtek
|
|
|
| Re: slow query [message #642 is a reply to message #641 ] |
Fri, 26 January 2007 08:34  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: slow query [message #643 is a reply to message #642 ] |
Fri, 26 January 2007 19:03 |
wojtekszala
Messages: 2
Registered: January 2007
Location: poland |
Junior Member |
|
|
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...
wojtek
|
|
|
Members
Search
Help
Register
Login
Home
