SQL query - memory overload

Question

I have this pretty simple SQL query

SELECT v.*, u.user_company as company_name, o.`name` as object_name,
o.address as object_address, i.id as installation_id, i.`name` 
as installation_name FROM maintenance as v, users as u,t_local as o,
t_local_objects as i WHERE o.active=1 AND v.done IS NOT NULL

The biggest table - maintenance, it has about 3k records, the others have around 300, which isn't really that much. When I execute it, I get a memory overload (tried to allocate over 130MB). How to optimize the query?

The tables will grow bigger in time, so it has to be valid for over 10k records in maintenance.

by not using joins, you are implicitly performing a cross join. matching every row of every table against every row of every other table, giving you a total result set of the number of entries in every table in the query, multiplied together. maintenance has 3k, if all the other tables have 1k rows, you have 3k * 1k * 1k rows in the result, or three billion rows - hence the memory usage — pala_
– pala_, Commented Apr 9, 2015 at 10:49

Rahul Tripathi · Accepted Answer · 2015-04-09 10:38:23Z

1

You are probably looking for JOINS. And btw 3K rows in a table is not much rather it is too less. JOIN the tables and your will get the results quickly.

Soemthing like this:

SELECT v.*, u.user_company as company_name, o.`name` as object_name,
o.address as object_address, i.id as installation_id, i.`name` 
as installation_name 
FROM maintenance as v inner join users as u on v.id = u.id
.....
WHERE o.active=1 AND v.done IS NOT NULL

answered Apr 9, 2015 at 10:38

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roh Codeur · Accepted Answer · 2015-04-09 10:40:17Z

0

There is an error in your sql query, you should be using joins.

Where do you get a memory load? Depending on the type of application which is using this query, you could try: 1. If its a web or desktop app, you could try pagination, for instance, limit records to a range 2. If you are running inside a sql tool such as squirrel or SQL Management studio, I would suggest checking where the error is coming from, is it an error in the tool? You could try setting up a stored proc which gives out results in a range

answered Apr 9, 2015 at 10:40

Roh Codeur

517 bronze badges

Collectives™ on Stack Overflow

SQL query - memory overload

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related