?
Solved

Using LIMIT without separating similar data

Posted on 2008-10-22
3
Medium Priority
?
214 Views
Last Modified: 2012-05-05
Hi, I'm using MySQL with PHP and I have a query similar to this:

SELECT i.ID, i.total, c.custname FROM tblinvoices i
LEFT JOIN tblcustomers c ON c.ID=i.custid
ORDER BY c.ID
LIMIT 0,1000

Is there a way to write this query to make it take less or more invoices depending on how many similar c.custnames there are at the end of the limit?  Basically, I don't want to set the limit stop at a point between a couple similar custnames, so I don't have to go to the next set of invoices (1000,2000) to get the rest of the similar custnames that were cut off at 1000.  I can't send the whole table and work with it in php because it exceeds the memory limits of php (there's a lot more than just 3 fields I'm getting from the real query).
0
Comment
Question by:Matt Kendall
[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
3 Comments
 
LVL 1

Expert Comment

by:nick_bal
ID: 22782395
Try to increase the limit in your php.ini file. The default is set to 8mb but you can change it to 32 or 64
memory_limit = 8M --->> memory_limit = 32M. See if it works.

0
 
LVL 23

Accepted Solution

by:
Tony McCreath earned 2000 total points
ID: 22806984
You could do a query to get the number of invoices for each of the customers, then do some maths to determine what limit values to use to ensure clean customer pages.

e.g. if it returns

customername, invoices
1, 400
2, 500
3, 200
4, 700
5, 350

you may choose your first page to be 0, 1100   (i.e. 400+500+200)

and the second page cound be 1100, 1050  


SELECT c.custname as customername, count(c.ID) as invoices  FROM tblcustomer c
LEFT JOIN tblinvoices i ON c.ID=i.custid
GROUP BY c.ID
ORDER BY c.ID

Open in new window

0
 
LVL 2

Author Closing Comment

by:Matt Kendall
ID: 31509022
great idea, thanks!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 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