473,320 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Nested Selects Causing MySQL 5.0.2 to CRASH!

I need some help guys. I am in the process of migrating a system from MS Access over to MySQL 5.0.2. The problem is with one particular query. It is this:

SELECT * FROM posted_loads WHERE 1=1 AND pl_dcity in (SELECT lastlinecity FROM zip_codes WHERE lat BETWEEN '38.9914439942' AND '44.7801560058' AND lon BETWEEN '84.7285439942' AND '90.5172560058') AND pl_dstate in (SELECT state FROM zip_codes WHERE lat BETWEEN '38.9914439942' AND '44.7801560058' AND lon BETWEEN '84.7285439942' AND '90.5172560058') ORDER BY pl_weight ASC

This particular query completely locks up MySQL while maxing out the CPU 100%. The only way out is to kill the process and restart mysqld.

Can somebody help me? I can't figure out what is causing the lockup. It happens every single time.

Thanks for any help!
Sean
Jan 22 '07 #1
2 1300
ronverdonk
4,258 Expert 4TB
It would help if there were indices on the lastlinecity and state columns.
I would try to do the WHERE In only once and create a temporary table that has the selected rows in it.
Expand|Select|Wrap|Line Numbers
  1. ## create the temp table
  2. CREATE TEMPORARY TABLE IF NOT EXISTS temp 
  3.                 (id int primary key auto_increment) 
  4.        SELECT lastlinecity, state FROM posted_loads 
  5.     WHERE lat 
  6.     BETWEEN '38.9914439942' AND '44.7801560058' 
  7.     AND lon 
  8.     BETWEEN '84.7285439942' AND '90.5172560058';
  9. ## then your query becomes
  10. SELECT * FROM posted_loads as t1, temp 
  11. WHERE 
  12.   t1.pl_dcity  = temp.lastlinecity 
  13. AND 
  14.   t1.pl_dstate = temp.state 
  15. ORDER BY pl_weight ASC
Ronald :cool:
Jan 23 '07 #2
Motoma
3,237 Expert 2GB
Perhaps the following SQL will work better:

Expand|Select|Wrap|Line Numbers
  1. SELECT posted_loads.*
  2. FROM posted_loads, zip_codes
  3. WHERE
  4.     lat BETWEEN '38.9914439942' AND '44.7801560058'
  5.     AND lon BETWEEN '84.7285439942' AND '90.5172560058'
  6.     AND pl_dcity = zip_codes.lastlinecity
  7.     AND pl_dstage = zip_codes.state
  8.  
Hope this helps,
Motoma
Jan 23 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary...
0
by: Knepley, Jim | last post by:
I'm basing some work on Joe Celko's excellent idea of using nested sets to represent an organizational structure as opposed to an adjacency list. By and large it's a great idea, but not without its...
4
by: Ka | last post by:
I install a mysql server in default installation with latin charset, but I want to use GBK(a chinese charset), so that I can store and search chinese words directly. so, I download, unpack and...
0
by: Jim C Nguyen | last post by:
I have a table with ~2.2 million rows. Sometimes when I do an update to one single row it will instead update ALL of the rows using the same update. This happens every one in about 500,000...
3
by: Brad | last post by:
I have an aspx web page which initially displays fine. When I postback the resulting response back to the client output is causing InternetExplorer 6 to crash. I've disabled the server side code...
110
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst...
2
by: Me LK | last post by:
I have a set of drop downs nested in a datagrid. Each drop down is a size or color for an item. Each row of the grid has a button which leads to a cart. When a user selects something from the...
5
by: q3537wh | last post by:
Has anyone encountered a situation where the @import is causing IE 6 to crash? I have a css file, let's call it test.css, that has only two lines in it. @import url("file1.css"); @import...
2
by: Alfons Nonell-Canals | last post by:
Hello, I've developed a program using python that have to connect to a mysql server several times. In a local machine (running the program in the same machine where the mysql server is) I have...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.