Announcement

Announcement Module
Collapse
No announcement yet.

index query on table with 14mm+ (and growing) records takes more than 2 seconds

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

  • #16
    It is hard to imagine that with queries taking 2-3 seconds you do not have any locking issues.

    You could try http://dev.mysql.com/doc/refman/5.0/...-profiles.html for checking how those 2-3 seconds are split among several tasks.

    Comment


    • #17
      Yes, what is your server actually doing?

      If I run a test on my laptop with 15m+ records with your table design and the average 20 rows per listid.
      I can do the first query in 0.27 seconds (before the index is load into key_cache) and consecutive queries in the <0.1 seconds time span.
      And that is with just 8MB key_cache.

      So I think you need to start by looking at what the server is actually doing instead of looking at what MySQL is doing.
      Is it swapping?
      Do you have high IO wait?
      etc.

      Good Luck! )

      Comment

      Working...
      X