The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> Database Management
|
SQlite - Slow query needs speedup
Discuss SQlite - Slow query needs speedup in the Database Management forum on Dev Shed.
SQlite - Slow query needs speedup Database Management forum discussing non-database specific SQL. Structured Query Language was designed to be a robust and standardized language for manipulating relational databases.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
September 9th, 2011, 07:43 AM
|
|
Registered User
|
|
Join Date: Dec 2004
Posts: 7
Time spent in forums: 1 h 32 m 37 sec
Reputation Power: 0
|
|
|
SQlite - Slow query needs speedup
Hi guys,
I have a somewhat large database. Important tables are
newsEvent ---> 800.000 entries
newsSentiment ---> 3 million entries
I have this query that brings more or less 5000 results:
Code:
SELECT ne.date, ne.hour, ns.companyId, ns.relevance, ne.ens,
ns.nip, ns.css, ne.category, ne.newsType
FROM newsEvent as ne, newsSentiment as ns
WHERE ns.newsId = ne.id AND ( ns.companyId = '0119FA'
|| ns.companyId = '33D96A') AND ne.date >= 20050101
AND ne.date <= 20101231 AND ns.relevance >= 75
ORDER BY ne.date, ne.hour
relevance and date are INTEGERS, companyId is char(7)
My database has indices for (among others):
newsEvent: date
newsSentiment: relevance, companyId
I am out of ideas on how to speed up this query, it is taking more or less 2 minutes to be executed. Any suggestions?
Thank you very much,
Chicoscience
|
September 9th, 2011, 07:56 AM
|
 |
Lost in code
|
|
|
|
|
Try adding an index on (ns.newsId, ns.companyId, ns.relevance)
|
September 9th, 2011, 09:02 AM
|
|
Registered User
|
|
Join Date: Dec 2004
Posts: 7
Time spent in forums: 1 h 32 m 37 sec
Reputation Power: 0
|
|
|
E-Oreo,
Your suggestion helped the time improve to 17 seconds, which is great!. Thank you very much! Any other suggestions are welcome.
|
September 9th, 2011, 03:48 PM
|
|
Lost in code
|
|
|
|
Run an EXPLAIN query on the query and post the results:
Code:
EXPLAIN SELECT ne.date, ne.hour, ns.companyId, ns.relevance, ne.ens,
ns.nip, ns.css, ne.category, ne.newsType
FROM newsEvent as ne, newsSentiment as ns
WHERE ns.newsId = ne.id AND ( ns.companyId = '0119FA'
|| ns.companyId = '33D96A') AND ne.date >= 20050101
AND ne.date <= 20101231 AND ns.relevance >= 75
ORDER BY ne.date, ne.hour
|
September 9th, 2011, 05:39 PM
|
|
Registered User
|
|
Join Date: Dec 2004
Posts: 7
Time spent in forums: 1 h 32 m 37 sec
Reputation Power: 0
|
|
Hi E-Oreo, thanks again, here are the results:
Code:
# |addr|opcode |p1 |p2 |p3|p4 |p5|comment
--+----+-------------+--------+-------+--+----------------+--+-------
1 |0 |Trace |0 |0 |0 | |00|
2 |1 |OpenEphemeral|2 |4 |0 |keyinfo(2,BINARY|00|
3 |2 |Integer |1000 |1 |0 | |00|
4 |3 |Integer |0 |2 |0 | |00|
5 |4 |MustBeInt |2 |0 |0 | |00|
6 |5 |IfPos |2 |7 |0 | |00|
7 |6 |Integer |0 |2 |0 | |00|
8 |7 |Add |1 |2 |3 | |00|
9 |8 |IfPos |1 |10 |0 | |00|
10|9 |Integer |-1 |3 |0 | |00|
11|10 |String8 |0 |4 |0 |0119FA |00|
12|11 |String8 |0 |5 |0 |33D96A |00|
13|12 |Integer |20050101|6 |0 | |00|
14|13 |Integer |20101231|7 |0 | |00|
15|14 |Integer |75 |8 |0 | |00|
16|15 |Goto |0 |82 |0 | |00|
17|16 |OpenRead |0 |10 |0 |7 |00|
18|17 |OpenRead |3 |24 |0 |keyinfo(1,BINARY|00|
19|18 |OpenRead |4 |1031837|0 |keyinfo(5,BINARY|00|
20|19 |Copy |6 |9 |0 | |00|
21|20 |SeekGe |3 |60 |9 |1 |00|
22|21 |Copy |7 |9 |0 | |00|
23|22 |IdxGE |3 |60 |9 |1 |01|
24|23 |Column |3 |0 |10| |00|
25|24 |IsNull |10 |59 |0 | |00|
26|25 |IdxRowid |3 |10 |0 | |00|
27|26 |Seek |0 |10 |0 | |00|
28|27 |Column |0 |0 |11| |00|
29|28 |IsNull |11 |59 |0 | |00|
30|29 |SeekGe |4 |59 |11|1 |00|
31|30 |IdxGE |4 |59 |11|1 |01|
32|31 |Column |4 |1 |13| |00|
33|32 |Concat |13 |4 |14| |00|
34|33 |Eq |14 |12 |13|collseq(BINARY) |71|
35|34 |Ne |5 |58 |12| |6a|
36|35 |Column |4 |2 |12| |00|
37|36 |Lt |8 |58 |12|collseq(BINARY) |6c|
38|37 |Column |3 |0 |16| |00|
39|38 |Column |0 |3 |17| |00|
40|39 |Column |4 |1 |18| |00|
41|40 |Column |4 |2 |19| |00|
42|41 |Column |0 |6 |20| |00|
43|42 |Column |4 |4 |21| |00|
44|43 |Column |4 |3 |22| |00|
45|44 |Column |0 |5 |23| |00|
46|45 |Column |0 |4 |24| |00|
47|46 |MakeRecord |16 |9 |12| |00|
48|47 |Column |3 |0 |25| |00|
49|48 |Column |0 |3 |26| |00|
50|49 |Sequence |2 |27 |0 | |00|
51|50 |Move |12 |28 |1 | |00|
52|51 |MakeRecord |25 |4 |13| |00|
53|52 |IdxInsert |2 |13 |0 | |00|
54|53 |IfZero |3 |56 |0 | |00|
55|54 |AddImm |3 |-1 |0 | |00|
56|55 |Goto |0 |58 |0 | |00|
57|56 |Last |2 |0 |0 | |00|
58|57 |Delete |2 |0 |0 | |00|
59|58 |Next |4 |30 |0 | |00|
60|59 |Next |3 |22 |0 | |00|
61|60 |Close |0 |0 |0 | |00|
62|61 |Close |3 |0 |0 | |00|
63|62 |Close |4 |0 |0 | |00|
64|63 |OpenPseudo |5 |12 |9 | |00|
65|64 |Sort |2 |80 |0 | |00|
66|65 |AddImm |2 |-1 |0 | |00|
67|66 |IfNeg |2 |68 |0 | |00|
68|67 |Goto |0 |79 |0 | |00|
69|68 |Column |2 |3 |12| |00|
70|69 |Column |5 |0 |16| |20|
71|70 |Column |5 |1 |17| |00|
72|71 |Column |5 |2 |18| |00|
73|72 |Column |5 |3 |19| |00|
74|73 |Column |5 |4 |20| |00|
75|74 |Column |5 |5 |21| |00|
76|75 |Column |5 |6 |22| |00|
77|76 |Column |5 |7 |23| |00|
78|77 |Column |5 |8 |24| |00|
79|78 |ResultRow |16 |9 |0 | |00|
80|79 |Next |2 |65 |0 | |00|
81|80 |Close |5 |0 |0 | |00|
82|81 |Halt |0 |0 |0 | |00|
83|82 |Transaction |0 |0 |0 | |00|
84|83 |VerifyCookie |0 |441 |0 | |00|
85|84 |TableLock |0 |10 |0 |newsEvent |00|
86|85 |TableLock |0 |13 |0 |newsSentiment |00|
87|86 |Goto |0 |16 |0 | |00|
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
