Member Avatar for vaishalinagare

hi,
i'm using LIMIT command in mysql query to retrieve top 9 records but it takes 10-12 min. pl help. thank you in advance

  • 5 Contributors
  • 6 Replies
  • 121 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

LIMIT itself is not the bottleneck — the query plan is. 's 10–12 minute run time points to a full table scan or an expensive filesort/temporary operation caused by missing or unusable indexes, an ORDER BY on a non‑indexed column, or a huge OFFSET. was right that LIMIT is fine when the engine can jump to rows quickly; and were right to flag indexing and full‑text as directions to investigate.

Practical steps (in order):

Run EXPLAIN on the exact SELECT to see rows, key, and Extra (look for "Using filesort" or "Using temporary"):

EXPLAIN SELECT col1, col2 FROM your_table WHERE <conditions> ORDER BY colX LIMIT 10;

If EXPLAIN shows no usable key, add an index that matches WHERE and ORDER BY. Composite indexes help when the same columns appear in both:

ALTER TABLE your_table ADD INDEX idx_cond_order (colUsedInWhere, colUsedInOrder);

Make queries covering (select only columns in the index) to avoid accessing the table. Avoid large OFFSET values; use keyset pagination instead:

SELECT id, colA FROM t WHERE id > last_id ORDER BY id ASC LIMIT 10;

For text searches, FULLTEXT on MyISAM (MySQL 4) can be far faster than wildcard LIKE; note its stopword/minimum length behavior. If search needs exceed what MySQL can do well, consider a dedicated search engine (Sphinx/Solr/etc.).

Checklist: EXPLAIN → add appropriate index(es) → reduce selected columns → change pagination strategy if using large OFFSET → re-run EXPLAIN and measure.

Recommended Answers

All 6 Replies

Member Avatar for nikesh.yadav

how many records are in ur database?
and i thought limit is best...

Member Avatar for nikesh.yadav

can u give us query

Member Avatar for pritaeas

Are you using indexes ?

Member Avatar for dickersonka

Agree with pritaeas, definitely use indexes.
Also if you have a lot of records, you probably don't want to use LIKE or 'string%'

take a look at full text searching if the indexes don't straighten up the issue

Member Avatar for Shanti C

hello dickersonka and pritaeas...
Please give clear explanation about mysql indexing...how to use??

Thanks in advance...

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.