Hi all. New to the forum. After doing a lot of research on this topic, I am wondering if our application is starting to push the limits of what MySQL can do "quickly."

We have a dual processor machine with 4G of RAM running LINUX 2.6 and MySQL v4.1. There is one table we are having difficulties with. It has about 1.6 million records. Schema is below...

+-------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------+-------+
| AID | smallint(5) unsigned | | PRI | 0 | |
| BID | mediumint(8) unsigned | | PRI | 0 | |
| C | mediumint(8) unsigned | | | 0 | |
| D | mediumint(8) unsigned | | | 0 | |
| F | tinyint(3) unsigned | | | 0 | |
+-------------+-----------------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

Doing a simple select seems to take on the long side, 0.06 sec.

mysql> select SQL_NO_CACHE count(*) FROM table WHERE AID = '3';
+----------+
| count(*) |
+----------+
| 40997 |
+----------+
1 row in set (0.06 sec)

I feel this is long relative to the simplicity of that query. If instead, I do the following query, it responds much quicker...

mysql> select SQL_NO_CACHE count(*) FROM table WHERE BID = '30011';
+----------+
| count(*) |
+----------+
| 49 |
+----------+
1 row in set (0.00 sec)

Both AID and BID are indexed...

mysql> show index from table;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| table | 0 | idx1 | 1 | AID | A | NULL | NULL | NULL | | BTREE | |
| table | 0 | idx1 | 2 | BID | A | 1628214 | NULL | NULL | | BTREE | |
| table | 1 | idx2 | 1 | BID | A | 46520 | NULL | NULL | | BTREE | |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

This test was done on a development server with no loading or no records being changed while the test was being performed. (times similar on production server during slow periods)

This delay becomes problematic as we use this table for our application which will join this table onto itself about 3 times. Doing this takes about 0.77 seconds which introduces a bottleneck when it is being accessed via our website.

It seems that when our queries involve a large number of rows, then the delay goes up signficantly. MySQL is said to work well with large tables, though, is there an issue when a large number of records are being accessed from a large table?

In a nutshell, is what I am seeing normal?

As you can see from show index, AID is indexed with a combined index over AID and BID whereas BID as an additional single field index. This means that the AID index is much bigger as it hold both fields data which may result in additional IO waits if the buffer does not fit into memory. Double check your mysql.cf for large enough key buffers if you want to keep to your index structure. Your machine seems to provide enough for 500 mb. If you use the default settings your main memory will not be touched!

We have 600M for the key buffer; that should be enough room. I did run a test where I created a single index on AID; though, that did not seem to improve speed. The more I look at it, the more it simply seems to be a function of having a large number of rows needing to be counted. The more rows that need to be counted, the longer it takes and the table's total row count seems to be less of a factor.