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

[snowwhite]

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:

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.

[maxymizer]

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).

[CROWHOST]

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.

[snowwhite]

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

[athlonkmf]

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.

[Korvan]

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.