query crashes sql server with a big database

[cwarn23]

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

SELECT `id` FROM `hashes` WHERE `sha1`="<º5AD@$;\"#"

Also my column structure is as follows

id    = char(7)
crc32 = char(5)
crc32b= char(5)
sha1  = char(12)

All of those columns are indexed. I have a 2GB index. Does anybody know why this query crashes my sql server?

[mwasif]

Post the EXPLAIN results of this query.

What is the server RAM and key_buffer_size in my.cnf?

[cwarn23]

The my.cnf file is as follows:

[mysqld]
skip-bdb
skip-innodb
ft_min_word_len=3

[myisamchk]
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:

EXPLAIN SELECT * FROM `hashes` LIMIT 2000000 , 1

id=1
select_type=SIMPLE
table=hashes
possible_keys=NULL
key=id
key_len=29
ref=NULL
rows=45128232
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.

[mwasif]

Post the results of the following query

EXPLAIN SELECT `id` FROM `hashes` WHERE `SHA1`="<º5AD@$;\"#"

[cwarn23]

The results are as follows:

id=1
select_type=SIMPLE
table=hashes
type=index
possible_keys=NULL
key=id
key_len=29
ref=NULL
rows=45404440
Extra=Using where; Using index

Any ideas?

[mwasif]

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

SHOW CREATE TABLE hashes

[cwarn23]

Below is the ouput.

CREATE TABLE `hashes` (
 `id` char(7) NOT NULL,
 `crc32` char(5) NOT NULL,
 `crc32b` char(5) NOT NULL,
 `sha1` char(12) NOT NULL,
 KEY `id` (`id`,`crc32`,`crc32b`,`sha1`)
) 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...

[mwasif]

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.

[cwarn23]

Even drop the crc32 and crc32b indexes since the where clause can also search the crc32 and crc32b columns?

[mwasif]

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?

[cwarn23]

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?

I just created 3 separate indexes for the three columns as suggested however my server was struggling to create the indexes for all 45 million rows. So I've emptied the tabled and changed the indexes and it will be about another 3 days before I find out if it worked. I shall post here if it doesn't work but hopefully the new database will be much more usable. Thanks for the great information and now I have a greater understanding of mysql indexes.