• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

MySQL query returns far too many rows (2 million)!

Experts,

I have a query that should return at most 25 rows - the results for one location in January 2013.

However the resultset has 2.5 million or so!

Obviously I'm missing something, b/c it looks like every row in every join table is being included in the results.

Here's the query.  Help!
sql.txt
0
Glen Gibb
Asked:
Glen Gibb
1 Solution
 
Shane KahkolaDirector of I.T.Commented:
Can you try SELECT DISTINCT?

If not, can you post some sample data that gets pulled from that query?
0
 
SarahDaisy8Commented:
This is probably way to simple of a thought to be the solution, but I'll throw it out there heh.  What about group by?
0
 
John_VidmarCommented:
Pull the SQL into a database tool, replace the select-section with select count(*), comment-out all tables except for the first join and run the query.  Add the next join, run the query... repeat these steps until you hit the table that makes your query blow up to 2 million records.  At this point, review the join (perhaps a typo).

Also, I would qualify every field reference, especially in the on-clause.
0
 
Glen GibbAuthor Commented:
Tnx, Experts, for your quick reply.

1.  (ccb) Yup, tried the DISTINCT modifier and reduced from a couple of million records to about 180 K.

2.  (sara) Attempted the GROUP BY, but things got REALLY slow, and still had superfluous results.

3. (john) Sounds like a plan.  I'll get started here.  Tell me please, how does one "qualify" a field reference?  I have made sure there are no ambiguous references, etc.  Am I missing something important?

I'll post sample results - no one needs a couple million of the same row!

Capt
0
 
Glen GibbAuthor Commented:
Thanks for the approach to solving this problem.

Turns out it was bad DB design.  Merged fields in the "consumption" and "consumption_item" tables to eliminate unnecessary duplication.

Presto!  MySQL workbench was able to help me analyse the difficulty.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now