MySQL Query IN() Clause Slow on Indexed Column

by

You are accessing 420 rows by primary key which will probably lead to an index access path. This could access 2 index pages and one data page per key. If these are in cache, the query should run fast. If not, every page access that goes to disk will incur the usual disk latency. If we assume 5ms disk latency and 80% cache hits, we arrive at 420*3*0.2*5ms=1.2 seconds which is on the order of what you’re seeing.

More Related Contents:

  1. Doing calculations in MySQL vs PHP
  2. Geo-Search (Distance) in PHP/MySQL (Performance)
  3. MySQL Performance – “IN” Clause vs. Equals (=) for a Single Value [duplicate]
  4. MySQL Stored Procedure vs. complex query
  5. How to avoid to display this error in PHP?
  6. Disappears \ from db value [closed]
  7. The requested URL was not found on this server.
  8. Query with multiple values in a column
  9. How to create a secure mysql prepared statement in php?
  10. Preserve Line Breaks From TextArea
  11. How can I loop through a MySQL result set more than once using the mysql_* functions?
  12. WAMP Cannot access on local network 403 Forbidden
  13. Doctrine 2 and Many-to-many link table with an extra field
  14. How do I give each registered user their own url using PHP?
  15. Post form and update multiple rows with mysql
  16. MySQL “NOT IN” query 3 tables
  17. How to install pdo driver in php docker image?
  18. Calculating distance between zip codes in PHP
  19. Access denied for user ‘root’@’localhost’ with PHPMyAdmin
  20. MySQL retrieve variable from Stored Procedure in PHP PDO
  21. Warning: mysql_query(): 3 is not a valid MySQL-Link resource
  22. Can’t insert Chinese character into MySQL
  23. Send data from android to server via JSON
  24. Dynamically create PHP object based on string
  25. How to build unlimited level of menu through PHP and mysql
  26. How to SELECT DEFAULT value of a field
  27. SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: nodename nor servname provided, or not known
  28. Laravel SQLSTATE[HY000] [2002] Connection refused
  29. How to avoid “Using temporary” in many-to-many queries?
  30. PHP 5 Reflection API performance

Leave a Comment