List:General Discussion« Previous MessageNext Message »
From:John Kopanas Date:November 27 2006 1:03am
Subject:Re: Re: Performance Problems With Two Tables With Over 500K Rows
View as plain text  
Thanks a lot for your help.

The query should and only does return 1-6 rows depending on the id.
Never more then that.  Here are the comperative EXPLAINs:

mysql> EXPLAIN SELECT * FROM purchased_services WHERE id = 1000;
+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table              | type  | possible_keys | key
   | key_len | ref   | rows | Extra |
+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | purchased_services | const | PRIMARY       |
PRIMARY |       4 | const |    1 |       |
+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM purchased_services WHERE company_id = 1000;
+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
| id | select_type | table              | type | possible_keys
              | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | purchased_services | ALL  |
purchased_services_company_id_index | NULL |    NULL | NULL | 626188 |
Using where |
+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Here is the explain for the SELECT COUNT(id)

mysql> EXPLAIN SELECT count(id) FROM companies;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key     |
key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | companies | index | NULL          | PRIMARY |
  4 | NULL | 533821 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.10 sec)

The explain takes a fraction of a second and returns the amound of row
plus some.  But when I just as for the count it took me 5 seconds.
Something is broken.

My innodb_buffer_pool_size is:
innodb_buffer_pool_size         | 8388608

That looks like 8MB... that sounds small if I have a DB with over 1M
rows to process.  No?

Thanks again for your help.

Your Friend,

John


On 11/25/06, Dan Nelson <dnelson@stripped> wrote:
> In the last episode (Nov 25), John Kopanas said:
> > Sorry about these questions.  I am used to working with DBs with less
> > then 10K rows and now I am working with tables with over 500K rows
> > which seems to be changing a lot for me.  I was hoping I can get some
> > people's advice.
> >
> > I have a 'companies' table with over 500K rows and a
> > 'purchased_services' table with over 650K rows.
> >
> > The following query takes over 6 seconds:
> > SELECT * FROM purchased_services WHERE (purchased_services.company_id = 535263)
>
> How many rows does it return, and what's its explain plan?
>
> > purchased_services.company_id has an index on it.
> >
> > The following query takes over 3 seconds:
> > SELECT count(id) FROM companies;
>
> An explain plan here would be useful too.
>
> > To me the time it takes to run these queries makes no sense.  I would
> > imagine both of these queries would take a fraction of a second.
> >
> > When running some of these queries and looking at 'SHOW processlist'
> > I was getting a lot of 'Writing to tmp'.
>
> That doesn't make much sense, since both queries should simply be doing
> index scans (or full table scans depending on how many rows are
> expected to match in the first query, but the explain plans will show
> that).
>
> > My DB engine is InnoDB.  I am running this on my laptop that is a
> > PowerBook 867 with 756 MB of Ram.
>
> What's your innodb_buffer_pool_size set to?
>
> --
>         Dan Nelson
>         dnelson@stripped
>


-- 
John Kopanas
john@stripped

http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
Thread
Performance Problems With Two Tables With Over 500K RowsJohn Kopanas25 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas26 Nov
    • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas26 Nov
      • Re: Re: Re: Performance Problems With Two Tables With Over 500K RowsDan Buettner26 Nov
        • Re: Re: Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson26 Nov
    • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
      • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson27 Nov
        • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
          • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson27 Nov
            • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
          • Re: Re: Performance Problems With Two Tables With Over 500KRowsmos27 Nov
        • Re: Performance Problems With Two Tables With Over 500K RowsDominik Klein27 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsDuncan Hill27 Nov