Solved

Problems with large MySQL table using ORDER BY and LIMIT

Posted on 2010-09-23
3
584 Views
Last Modified: 2013-12-16
I have a MySQL table that is pretty huge. There are 21 columns and (at this point) about 3 million rows. The interface I'm writing in C# to display data from this table will obviously need the ability to break results into pages. The way I was going to do this was using LIMIT. All results would be sorted by date. So the query for the first page would look like this:

   SELECT * FROM BigTable ORDER BY ItemDate ASC LIMIT 0,250;

This takes forever though, because before it applies the limit it goes through and orders the entire table by the date field. So I'm not really sure how to speed this up and make it work efficiently. Any recommendations?
0
Comment
Question by:elorc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 33746540
Hi elorc,

There are a couple of ways.  :)

Is there an Identity column on the table?  That's an easy solution.

Is the ItemDate column indexed?  Assuming so, try the code below.


Kent

SELECT *
FROM
(
  SELECT ItemDate 
  FROM BigTable 
  ORDER BY ItemDate ASC 
  LIMIT 0,250
) t0
LEFT JOIN BigTable
  ON t0.ItemDate = BigTable.ItemDate;

Open in new window

0
 
LVL 1

Author Closing Comment

by:elorc
ID: 33747852
That makes a ridiculous difference. Thank you.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 33747938
Hi elorc,

Just so you know how this works, MySQL recognizes that there is an index on the ItemDate column and grabs the first 250 dates from the index instead of reading the table.

It then joins only those 250 rows from the table.



Kent
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question