943,162 Members | Top Members by Rank

Web Development > Databases > MySQL > query crashes sql server with a big database
Ad:
  • MySQL Discussion Thread
  • Unsolved
  • Views: 1456
  • MySQL RSS
You are currently viewing page 1 of this multi-page discussion thread
Permalink
Jan 12th, 2010
0

query crashes sql server with a big database

Expand Post »
Hi and I have a database with over 32 million entries and whenever I try the below mysql query or simular it crashes the sql server unless I use phpmyadmin. I have 4 columns all indexed and all are the char() type with a fixed length. Below is an example query
MySQL Syntax (Toggle Plain Text)
  1. SELECT `id` FROM `hashes` WHERE `SHA1`="<º5AD@$;\"#"
Also my column structure is as follows
MySQL Syntax (Toggle Plain Text)
  1. id    = CHAR(7)
  2. CRC32 = CHAR(5)
  3. crc32b= CHAR(5)
  4. SHA1  = CHAR(12)
All of those columns are indexed. I have a 2GB index. Does anybody know why this query crashes my sql server?
Sponsor
Featured Poster
Reputation Points: 410
Solved Threads: 258
Occupation: Genius
cwarn23 is offline Offline
3,003 posts
since Sep 2007
Permalink
Jan 14th, 2010
0
Re: query crashes sql server with a big database
Post the EXPLAIN results of this query.

What is the server RAM and key_buffer_size in my.cnf?
Last edited by mwasif; Jan 14th, 2010 at 1:32 pm.
Reputation Points: 29
Solved Threads: 47
Posting Whiz
mwasif is offline Offline
312 posts
since Dec 2007
Permalink
Jan 14th, 2010
0
Re: query crashes sql server with a big database
The my.cnf file is as follows:
MySQL Syntax (Toggle Plain Text)
  1. [mysqld]
  2. skip-BDB
  3. skip-INNODB
  4. ft_min_word_len=3
  5.  
  6. [myisamchk]
  7. ft_min_word_len=3
Also as for the ram I have about 700MB boostable. And I couldn't find the key_buffer_size. Also the result for the explain select query is as follows:
MySQL Syntax (Toggle Plain Text)
  1. EXPLAIN SELECT * FROM `hashes` LIMIT 2000000 , 1
  2.  
  3. id=1
  4. select_type=SIMPLE
  5. table=hashes
  6. possible_keys=NULL
  7. key=id
  8. key_len=29
  9. ref=NULL
  10. rows=45128232
  11. Extra=USING index
Does this give any clues as to what the problem is because if I try to view the last few rows in phpmyadmin that also crashes the sql server. It is as if mysql will only read the first so many rows before crashing.
Sponsor
Featured Poster
Reputation Points: 410
Solved Threads: 258
Occupation: Genius
cwarn23 is offline Offline
3,003 posts
since Sep 2007
Permalink
Jan 14th, 2010
0
Re: query crashes sql server with a big database
Post the results of the following query
MySQL Syntax (Toggle Plain Text)
  1. EXPLAIN SELECT `id` FROM `hashes` WHERE `SHA1`="<º5AD@$;\"#"
Reputation Points: 29
Solved Threads: 47
Posting Whiz
mwasif is offline Offline
312 posts
since Dec 2007
Permalink
Jan 14th, 2010
0
Re: query crashes sql server with a big database
The results are as follows:
MySQL Syntax (Toggle Plain Text)
  1. id=1
  2. select_type=SIMPLE
  3. table=hashes
  4. type=index
  5. possible_keys=NULL
  6. key=id
  7. key_len=29
  8. ref=NULL
  9. rows=45404440
  10. Extra=USING WHERE; USING index
Any ideas?
Sponsor
Featured Poster
Reputation Points: 410
Solved Threads: 258
Occupation: Genius
cwarn23 is offline Offline
3,003 posts
since Sep 2007
Permalink
Jan 14th, 2010
0
Re: query crashes sql server with a big database
Your query is scanning 45404440 rows in the table to get the desired results. How many rows do you have in the table? Post the table structure with
MySQL Syntax (Toggle Plain Text)
  1. SHOW CREATE TABLE hashes
Reputation Points: 29
Solved Threads: 47
Posting Whiz
mwasif is offline Offline
312 posts
since Dec 2007
Permalink
Jan 14th, 2010
0
Re: query crashes sql server with a big database
Below is the ouput.
MySQL Syntax (Toggle Plain Text)
  1. CREATE TABLE `hashes` (
  2.  `id` CHAR(7) NOT NULL,
  3.  `CRC32` CHAR(5) NOT NULL,
  4.  `crc32b` CHAR(5) NOT NULL,
  5.  `SHA1` CHAR(12) NOT NULL,
  6.  KEY `id` (`id`,`CRC32`,`crc32b`,`SHA1`)
  7. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
Also I currently have 45457356 rows and the number is increasing by the minute. Is there a flaw in my database design as I don't mind deleting the table and starting with a new design...
Sponsor
Featured Poster
Reputation Points: 410
Solved Threads: 258
Occupation: Genius
cwarn23 is offline Offline
3,003 posts
since Sep 2007
Permalink
Jan 14th, 2010
1
Re: query crashes sql server with a big database
You had created a composite index on all the index. Drop this index and create index on sha1 only. This article will help you to understand how the indexes should be created.
Reputation Points: 29
Solved Threads: 47
Posting Whiz
mwasif is offline Offline
312 posts
since Dec 2007
Permalink
Jan 14th, 2010
0
Re: query crashes sql server with a big database
Even drop the crc32 and crc32b indexes since the where clause can also search the crc32 and crc32b columns?
Sponsor
Featured Poster
Reputation Points: 410
Solved Threads: 258
Occupation: Genius
cwarn23 is offline Offline
3,003 posts
since Sep 2007
Permalink
Jan 15th, 2010
1
Re: query crashes sql server with a big database
If you are individually have these columns in WHERE clause then create indexes separately on each column and drop them from composite index. Did you read the article I provided?
Reputation Points: 29
Solved Threads: 47
Posting Whiz
mwasif is offline Offline
312 posts
since Dec 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: mysql error 1060 42000
Next Thread in MySQL Forum Timeline: How can I speed up multi select query?





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC