Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have Employee Table - 80K records about 100 fields

I have an app table - 8K records and 30 fields

Then a subset of employee table also stored as table that has our divisions subset - call this Division table - 3k records and 100 fields

So I created 3 views:

  1. Added Employee Table to App Table on Employee ID... takes 4 seconds for the 8k records
  2. Inner JOIN App Table to Division table on Emp ID... takes 7 seconds for 3K records
  3. LEFT OUTER JOIN on App Table comparing Division Emp ID... takes 17 seconds for 5k records

So two related questions:

  1. Do the times seem right? I have Emp ID indexed.
  2. Why don't the times get faster when processing the view again?

Explain output on Left Outer Join:

    ---+-------------+----------------------------+--------+---------------------+---------+---------+--------------------------------+------+--------------------------+
| id | select_type | table                      | type   | possible_keys       | key     | key_len | ref                            | rows | Extra                    |
+----+-------------+----------------------------+--------+---------------------+---------+---------+--------------------------------+------+--------------------------+
|  1 | SIMPLE      | smart_userstest            | ALL    | PRIMARY,uidforsmart | NULL    | NULL    | NULL                           | 6937 | NULL                     |
|  1 | SIMPLE      | smart_userstest            | eq_ref | PRIMARY,uidforsmart | PRIMARY | 767     | testgiver.smart_userstest.TRID |    1 | Using where; Using index |
|  1 | SIMPLE      | reports_4regions_all_karen | index  | NULL                | 4region | 869     | NULL                           | 2784 | Using where; Using index |
|  1 | SIMPLE      | ldap_karen                 | eq_ref | PRIMARY,mainindex   | PRIMARY | 32      | testgiver.smart_userstest.TRID |    1 | Using where              |
+----+-------------+----------------------------+--------+---------------------+---------+---------+--------------------------------+------+--------------------------+
share|improve this question
    
Show your queries with EXPLAIN. –  Mihai Jan 30 at 21:16
    
@Mihai - I have used EXPLAIN. What info do you need from it? –  LOSTinDB Jan 30 at 21:25
2  
possible keys,key ,rows,extra..Also edit your post with existing indexes.Here`s an example dba.stackexchange.com/questions/27074/… –  Mihai Jan 30 at 21:31
    
@Mihai - updated with explain output –  LOSTinDB Jan 30 at 21:49
    
Also your query. –  Mihai Jan 30 at 22:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.