•  

Announcement

Announcement Module
Collapse
No announcement yet.

should a count with an index take this long?

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • should a count with an index take this long?

    #1
    CREATE TABLE `WikiParagraphs2` ( `ID` int(9) unsigned NOT NULL auto_increment, `Paragraph` text collate utf8_swedish_ci NOT NULL, PRIMARY KEY (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci AUTO_INCREMENT=73035277 ;mysql> select count(ID) from WikiParagraphs2 where ID < 9999999;+-----------+| count(ID) |+-----------+| 7375964 | +-----------+1 row in set (44.79 sec)mysql> explain select count(ID) from WikiParagraphs2 where ID < 9999999;+----+-------------+-----------------+-------+---------------+---------+---------+------+---------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------------+-------+---------------+---------+---------+------+---------+--------------------------+| 1 | SIMPLE | WikiParagraphs2 | range | PRIMARY | PRIMARY | 4 | NULL | 7997361 | Using where; Using index | +----+-------------+-----------------+-------+---------------+---------+---------+------+---------+--------------------------+1 row in set (0.05 sec)



    It says it's using an index so I don't understand why it would take 45 seconds to get the count...any ideas?

    Edit: Added table definition.
    Tags: None

  • #2
    How large is your key_buffer_size?

    But you should also consider that even though it is using an index it has to examine 7,997,361 rows.

    Which means that each row is handled in about 5 microseconds or 177,719 rows per second, which isn't a half bad figure.

    The important thing and content of this is that you shouldn't expect miracles even though it is using an index.

    when you run a COUNT(col1) it means that is has to check the value of col1 and determine if it is zero or NULL before we are addint it to the count as normal.

    Comment


    • #3
      Even though it has NOT NULL for the primary key field it still has to check that?

      My key_buffer_size is set to 16MB.

      I'll try increasing it to see if it helps. What is a good value for that on a server that is dedicated to mysql and apache?

      Comment


      • #4
        The key_buffer_size is depending a bit on how much memory other applications (apache in our case) needs.
        But the general rule of thumb is 25% of the available RAM should be used as key_cache.
        The rest should be left unused so that the OS can use it for file cache which is what MyISAM tables are relying on since they don't have an internal data cache.
        Like for example InnoDB where indexes and data is cached internally in the same memory pool.

        BTW: I should have noticed it earlier, your query should be written with SELECT count(*) FROM ... instead of SELECT count(ID) FROM ... because you are after the number of rows and not the nr of ID's.
        It makes it easier to read the query and understand what you are after.
        And possibly it could have an effect on performance as well, but I'm not sure.

        Comment


        • #5
          Ahhh I see.

          I raised the key size to 128MB yesterday but I've got 4gb on the box so perhaps I should raise it a bit higher )

          I read somewhere that using /dev/shm is good for tmpfs - is that really true or should I not be doing that?

          Current stats on my db have the following info:

          http://www.pduck.com/status.txt

          Thanks for the help!

          Comment

          Working...
          X