Member Avatar for leakbali

I have more than 420,000 rows articles in my table. I just want to query a data which is has only one row, but it takes too long.. any suggestion?

database : mysql, Storage engine : MyISAM

mysql_query("SELECT id, title, ... ,postdate FROM table_name WHERE id = 1

the result only one row.. but it take too long to open the page that query on more than 420,000 rows table

Edited by happygeek because: fixed formatting
  • 3 Contributors
  • 5 Replies
  • 181 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by mwasif

Dani AI

Generated

Brief summary and targeted checks for this symptom.

reports a single-row lookup from a ~420k (and later ~1M) row MyISAM table that still feels slow even though an index exists and EXPLAIN was reported as “good” (per and ). When EXPLAIN shows index usage but the page is slow, the real bottleneck is commonly outside the index seek: large column transfer, PHP-side work, server I/O or cache misses, table/index fragmentation, or network latency between web and DB servers.

Helpful isolation steps (run these to narrow the layer causing delay):

  • Measure the raw query time from the database server (run the same SELECT in the mysql client) and compare to the web-page time. Fast in the client + slow in PHP points at app/network work.
  • Confirm which columns are fetched; avoid returning large TEXT/BLOB fields when only id/title/postdate are needed.
  • Verify actual index details and index hit behavior rather than presence only.
  • Check server I/O and MyISAM index cache behavior (key buffer) and look for high iowait or table-level locking.
  • If many deletes/updates occurred, consider index/table maintenance (ANALYZE/OPTIMIZE) — with caution about locks.

Useful commands to gather facts:

SHOW INDEX FROM table_name;
ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;
SHOW GLOBAL STATUS LIKE 'Key_reads';
SHOW GLOBAL STATUS LIKE 'Key_read_requests';

For focused diagnosis, the most useful artifacts are: EXPLAIN output, the table DDL, a short timing from the mysql client, and a note about any large TEXT/BLOB columns being selected. As already requested those outputs, they will indicate whether the delay is on the DB side (I/O, cache) or in the application/network layer and point to the most effective fix (adjust buffers, reduce transferred columns, add caching, or optimize table layout).

Recommended Answers

All 5 Replies

Member Avatar for debasisdas

do you have index on id column ?

Member Avatar for leakbali

yup, i have it

Member Avatar for mwasif

Post the output of

EXPLAIN SELECT id, title, ... ,postdate FROM table_name WHERE id = 1
Member Avatar for leakbali

Post the output of

EXPLAIN SELECT id, title, ... ,postdate FROM table_name WHERE id = 1

Everything is work, the output work good. But the problem is it take a long time to open the page.

This is the same problem when i work with 1,000,000 rows. it take a long long time to open the page. Is there any explanation about this? do i need to use a cache in the query?

Member Avatar for mwasif

Post the output of EXPLAIN so that I can identify the problem. Also post the table structure preferably the out put of

SHOW CREATE TABLE table_name;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.