hosted by liquidweb


Go Back   Web Hosting Talk : Web Hosting Main Forums : Web Hosting Talk Tutorials : Programming Tutorials : How a Simple MySQL query crush the server
Reply

Forum Jump

How a Simple MySQL query crush the server

Reply Post New Thread In Programming Tutorials Subscription
 
Send news tip View All Posts Thread Tools Search this Thread Display Modes
  #1  
Old 11-09-2005, 05:49 AM
snowwhite snowwhite is offline
Newbie
 
Join Date: Feb 2003
Posts: 29

How a Simple MySQL query crush the server


Hi everyone.

I'm having issues with my host over my MySQL database, in particular a table that records my site members which has over 85000 records.

These are the two queries that my host had problems with:

Code:
select count(id) from table where status != 'pending'
select name, points from table order by points desc limit 7
id - autoincrement field, int 7
name - varchar 32
points - int 5

Apparently, each of these query manage to crash the MySQL server at some point and the tech guy resorted to renaming the table so that the queries failed, resulting in massive MySQL errors on my site.

Can someone advise me if there's any way I can prevent these two queries from crashing the servers and for future queries as well? I can't seem to simplify the queries any further.

Thank you.



Sponsored Links
  #2  
Old 11-09-2005, 06:07 AM
maxymizer maxymizer is offline
Web Hosting Evangelist
 
Join Date: Apr 2005
Posts: 521
Run "EXPLAIN SELECT(put your query here now, without ())".
Which MySQL version are you using?

This seems to be issue with indexing (create index on points, that should speed up your second query).

  #3  
Old 11-11-2005, 06:46 AM
CROWHOST CROWHOST is offline
Junior Guru
 
Join Date: Jan 2005
Location: Chicago
Posts: 226
A text search on the column status will be more taxing than just making it an integer which represents the status numerically- 1=pending, 2=cancelled, 3=completed etc.

__________________
Ken

CROWHOST hosting+colocation services | 877-CROWHOST | support at crowhost.com
Independent remote-hands serving all Chicago data centers

Sponsored Links
  #4  
Old 11-14-2005, 09:08 AM
snowwhite snowwhite is offline
Newbie
 
Join Date: Feb 2003
Posts: 29
Thank you. Running explain on both queries give me the following:-

id | Select Type | table | type | possible_keys | key | key len | ref | rows | extra
1 | Simple | member | all | member_status | | | | all | using where
1 | Simple | member | index | member_points | 2 | | | 33019 | using where

For some reasons, the first query seems to be running thru' the entire table. I'm guessing is because the query ran thru all 86000+ row each time its executed and it was being executed quite frequently that brought down the database server.

I believed the MySQL version is 4.1.14

  #5  
Old 11-22-2005, 12:07 PM
athlonkmf athlonkmf is offline
Junior Guru
 
Join Date: Apr 2004
Posts: 185
seems that there is no key on the status-field so a full tablescan is required.
Also, depending on the table-type, count()-statements can be slow. innoDB is a major hog on counts, myisam is much faster.

Nevertheless, instead of counting it constantly you can chose to make a cachingtable that hold the total of not-pending customers so you don't have to count.

Or you can just do a select of id's and then use num_rows of whatever language you're using (prob php) to count them. Meaning, don't let the DB count for you.

  #6  
Old 11-23-2005, 05:56 PM
Korvan Korvan is offline
Web Hosting Guru
 
Join Date: Nov 2005
Posts: 268
You can probably do what CROHOST suggested by first creating a new table column that takes an int(2) value then run 3 queries to populate it with a correct value. Then delete the old column, and rename your new one. Just by doing that you will speed up your query drastically.

If you want to be fancy you could use an ENUM data type.

Then index that column so the query can skip the first step (scanning the entire database for everything matching the where statement) and just do the second step (counting the result).

You can either use a CREATE INDEX query to do this or use an ALTER TABLE query using ADD INDEX.

Reply

Related posts from TheWhir.com
Title Type Date Posted
Google Releases Hosted Database Service Cloud SQL to General Availability Web Hosting News 2014-02-12 13:46:02
Google Cloud Provides Support For Native MySQL Connections Web Hosting News 2013-11-01 14:36:06
Alert Logic Releases New Version of Security and Compliance Solution Web Hosting News 2013-02-19 15:55:16
Web Host AIT Releases New Version of Server Management Control Panel Web Hosting News 2012-08-27 13:23:00
French Web Host Gandi Expands Platform as a Service to US Market Web Hosting News 2012-07-09 10:40:52


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Postbit Selector

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump
Login:
Log in with your username and password
Username:
Password:



Forgot Password?
Advertisement:
Web Hosting News:



 

X

Welcome to WebHostingTalk.com

Create your username to jump into the discussion!

WebHostingTalk.com is the largest, most influentual web hosting community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?