Ulrik Petersen wrote:
> Hi,
>
>> Hi, I am having a problem with the following query. It seems to
>> force php to timeout after 30secs. The query goes through 150K
>> records. Is there anything I can do to speed it up?
>>
>> code-
>> SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time,
>> duration, cost, U.firstname AS firstname, U.surname AS surname
>> FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no
>> WHERE C.stamptime >= $unixtimestart
>> AND C.stamptime <= $unixtimeend
>> AND direction = 'Out'
>> ORDER BY cost desc LIMIT 0,16
>> --
>
> You can try using "C.stamptime BETWEEN $unixtimestart AND
> $unixtimeend" instead, and then put an index on C.stamptime. I have
> found that BETWEEN ... AND ... is faster than the "<= AND >="
> version, especially if you put an index on the column. If you look
> at the VM code with EXPLAIN, you will see why.
>
>>
>> Lloydie-t
>
> Ulrik P.
Lloyd and Ulrik,
Don't use the "BETWEEN x AND y" syntax! It always does a table scan even if
there is an index available. Use two seperate comparisons. The explain
output below shows the four cases for some sample tables of mine:
compares no index
between no index
compraes with index
between with index
with no index
select * from device_property_value where device_property_id >= 100 and
device_property_id <= 200
0 Goto 0 18
1 Integer 0 0
2 OpenRead 0 63
3 SetNumColumns 0 3
4 Rewind 0 16
5 Column 0 1
6 Integer 100 0
7 Lt 26881 15 collseq(BINARY)
8 Column 0 1
9 Integer 200 0
10 Gt 26881 15 collseq(BINARY)
11 Column 0 0
12 Column 0 1
13 Column 0 2
14 Callback 3 0
15 Next 0 5
16 Close 0 0
17 Halt 0 0
18 Transaction 0 0
19 VerifyCookie 0 47
20 Goto 0 1
21 Noop 0 0
select * from device_property_value where device_property_id between 100 and
200
0 Goto 0 20
1 Integer 0 0
2 OpenRead 0 63
3 SetNumColumns 0 3
4 Rewind 0 18
5 Column 0 1
6 Dup 0 0
7 Integer 100 0
8 Ge 26880 11 collseq(BINARY)
9 Pop 1 0
10 Goto 0 17
11 Integer 200 0
12 Gt 26881 17 collseq(BINARY)
13 Column 0 0
14 Column 0 1
15 Column 0 2
16 Callback 3 0
17 Next 0 5
18 Close 0 0
19 Halt 0 0
20 Transaction 0 0
21 VerifyCookie 0 49
22 Goto 0 1
23 Noop 0 0
with index on device_property_value(device_property_id)
select * from device_property_value where device_property_id >= 100 and
device_property_id <= 200
0 Goto 0 32
1 Integer 0 0
2 OpenRead 0 63
3 SetNumColumns 0 3
4 Integer 0 0
5 OpenRead 1 178 keyinfo(1,BINARY)
6 Integer 200 0
7 NotNull -1 10
8 Pop 1 0
9 Goto 0 29
10 MakeRecord 1 0 i
11 MemStore 0 1
12 Integer 100 0
13 NotNull -1 16
14 Pop 1 0
15 Goto 0 29
16 MakeRecord 1 0 i
17 MoveGe 1 29
18 MemLoad 0 0
19 IdxGE 1 29 +
20 RowKey 1 0
21 IdxIsNull 1 28
22 IdxRecno 1 0
23 MoveGe 0 0
24 Column 0 0
25 Column 0 1
26 Column 0 2
27 Callback 3 0
28 Next 1 18
29 Close 0 0
30 Close 1 0
31 Halt 0 0
32 Transaction 0 0
33 VerifyCookie 0 48
34 Goto 0 1
35 Noop 0 0
select * from device_property_value where device_property_id between 100 and
200
0 Goto 0 20
1 Integer 0 0
2 OpenRead 0 63
3 SetNumColumns 0 3
4 Rewind 0 18
5 Column 0 1
6 Dup 0 0
7 Integer 100 0
8 Ge 26880 11 collseq(BINARY)
9 Pop 1 0
10 Goto 0 17
11 Integer 200 0
12 Gt 26881 17 collseq(BINARY)
13 Column 0 0
14 Column 0 1
15 Column 0 2
16 Callback 3 0
17 Next 0 5
18 Close 0 0
19 Halt 0 0
20 Transaction 0 0
21 VerifyCookie 0 48
22 Goto 0 1
23 Noop 0 0
As you can see, only the third case uses the index.
If the range of value that meet your conditions is small relative to the
size of your table, using the index can be much faster. If your table has
150K records then a table scan will look at all 150K of them. If your range
selects 1000 records in the middle, the index will be used to locate the
first matching record, and then it will scan the 1000 records of interest
and stop when it finds the first record greater than the maximum value.
I don't know why BETWEEN doesn't use an index, but it doesn't, so stick to
comparisons. The VDBE code produced is larger, but it executes much faster.
Dennis Cote
