#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    7
    Rep 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
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Try adding an index on (ns.newsId, ns.companyId, ns.relevance)
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    7
    Rep 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.
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    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
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2004
    Posts
    7
    Rep 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|

IMN logo majestic logo threadwatch logo seochat tools logo