Performance problem or reached the limit of MySQL?

MySQL & MariaDB Other MySQL® Questions
1

Hi all!

First of all, congratulations for the Web :slight_smile:

I think I’m having performance problems with a very small MyIsam table but I’m not sure if it’s a configuration/design/hardware problem or have I reached the limit of MySQL.

The table is very simple and small (about 20000 rows). It contains the price and a short description of some products, and a timestamp indicating the moment of the insert:

CREATE TABLE products (
id int(11) NOT NULL default ‘0’,
timestamp int(11) NOT NULL default ‘0’,
price int(11) NOT NULL default ‘0’,
description varchar(256) NOT NULL default ‘’,
KEY idx_products_id (id),
KEY idx_products_timestamp (timestamp),
KEY idx_products_price (price)
) ENGINE=MyISAM

The database host is Intel Xeon 2.80GHz-4Gb RAM-Linux (kernel 2.6.15) using my-huge.cnf, and I’m querying the database through a separate Apache+PHP host using persistent connections.

The query is also very simple:

SELECT * FROM products WHERE price > $value1 AND an_precio < $value2 ORDER BY (timestamp) DESC LIMIT 100;

With random $value1 and $value2, mytop shows only 50 qps (no inserts or updates simultaneously, just this query).

After reading some useful posts on the Web, I removed idx_products_price index ( IGNORE INDEX(idx_products_price) ). This modification increases the qps to 350 (is normal that increase with only ignoring that index??), but I think the value of 350 qps is still low. My question is if is it a performance problem or have I reached the limit of this MySQL configuration and 350 qps is a good value?

Thanks in advance for your comments.

Regards,
Nacho.

2

Hi,

This is not MySQL taken to its limit. 20.000 rows table is very small.

First you probably did not convert query fully:

SELECT * FROM products WHERE price > $value1 AND an_precio < $value2 ORDER BY (timestamp) DESC LIMIT 100;

There is no an_precio column in the table so is it price ?

If so MySQL has to options running this query - first is to use range on price index do full sort on the range and return rows. This works well if range is small.

Second to retrieve rows in sorted order using index on timestamp - this works well if range is large so it is easy to find rows from this range doing reverse index scan.

Sometimes neither of this could be effective enough.

One solution is to use fixed price ranges, for example#
1-100$
100-400$ etc and pass price_range=5 rather than real price.
In his case (price_range,timestamp) index would be very efficient.

I blogged yesterday about very similar problem:

http://www.mysqlperformanceblog.com/2006/08/10/using-union-t o-implement-loose-index-scan-to-mysql/

You also can use similar approach to use number of unios with order by and apply extra price filtering clause if you want partial pre-computed ranges.

I know this is ugly…