Mysql select - improve performances

Question

Hey,

I am working on an e-shop which sells products only via loans. I display 10 products per page in any category, each product has 3 different price tags - 3 different loan types. Everything went pretty well during testing time, query execution time was perfect, but today when transfered the changes to the production server, the site "collapsed" in about 2 minutes. The query that is used to select loan types sometimes hangs for ~10 seconds and it happens frequently and thus it cant keep up and its hella slow. The table that is used to store the data has approximately 2 milion records and each select looks like this:

SELECT * 
FROM products_loans 
WHERE KOD IN("X17/Q30-10", "X17/12", "X17/5-24") 
AND 369.27 BETWEEN CENA_OD AND CENA_DO;

3 loan types and the price that needs to be in range between CENA_OD and CENA_DO, thus 3 rows are returned.

But since I need to display 10 products per page, I need to run it trough a modified select using OR, since I didnt find any other solution to this. I have asked about it here, but got no answer. As mentioned in the referencing post, this has to be done separately since there is no column that could be used in a join (except of course price and code, but that ended very, very badly). Here is the show create table, kod and CENA_OD/CENA_DO very indexed via INDEX.

CREATE TABLE `products_loans` (
  `KOEF_ID` bigint(20) NOT NULL,
  `KOD` varchar(30) NOT NULL,
  `AKONTACIA` int(11) NOT NULL,
  `POCET_SPLATOK` int(11) NOT NULL,
  `koeficient` decimal(10,2) NOT NULL default '0.00',
  `CENA_OD` decimal(10,2) default NULL,
  `CENA_DO` decimal(10,2) default NULL,
  `PREDAJNA_CENA` decimal(10,2) default NULL,
  `AKONTACIA_SUMA` decimal(10,2) default NULL,
  `TYP_VYHODY` varchar(4) default NULL,
  `stage` smallint(6) NOT NULL default '1',
 PRIMARY KEY  (`KOEF_ID`),
 KEY `CENA_OD` (`CENA_OD`),
 KEY `CENA_DO` (`CENA_DO`),
 KEY `KOD` (`KOD`),
 KEY `stage` (`stage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And also selecting all loan types and later filtering them trough php doesnt work good, since each type has over 50k records and the select takes too much time as well...

Any ides about improving the speed are appreciated.

Edit:

Here is the explain

+----+-------------+----------------+-------+---------------------+------+---------+------+--------+-------------+
| id | select_type | table          | type  | possible_keys       | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+-------+---------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | products_loans | range | CENA_OD,CENA_DO,KOD | KOD  | 92      | NULL | 190158 | Using where |
+----+-------------+----------------+-------+---------------------+------+---------+------+--------+-------------+

I have tried the combined index and it improved the performance on the test server from 0.44 sec to 0.06 sec, I cant access the production server from home though, so I will have to try it tomorrow.

Answer 1

Try to refactor your query like:

SELECT * FROM products_loans 
WHERE KOD IN("X17/Q30-10", "X17/12", "X17/5-24") 
AND CENA_OD >= 369.27
AND CENA_DO <= 369.27;

(mysql is not very smart when choosing indexes) and check the performance.

The next try is to add a combined key - (KOD,CENA_OD,CENA_DO)

And the next major try is to refactor your base to have products separated from prices. This should really help.

PS: you can also migrate to postgresql, it's smarter than mysql when choosing right indexes.

Answer 2

Your issue is that you are searching for intervals which contain a point (rather than the more normal query of all points in an interval). These queries do not work well with the standard B-tree index, so instead you need to use an R-Tree index. Unfortunately MySQL doesn't allow you to select an R-Tree index on a column, but you can get the desired index by changing your column type to GEOMETRY and using the geometric functions to check if the interval contains the point.

See Quassnoi's article Adjacency list vs. nested sets: MySQL where he explains this in more detail. The use case is different, but the techniques involved are the same. Here's an extract from the relevant part of the article:

There is also a certain class of tasks that require searching for all ranges containing a known value:

• Searching for an IP address in the IP range ban list
• Searching for a given date within a date range

and several others. These tasks can be improved by using R-Tree capabilities of MySQL.

Answer 3

MySQL can only use 1 key. If you always get the entry by the 3 columns, depending on the actual data (range) in the columns one of the following could very well add a serious amount of performance:

ALTER TABLE products_loans ADD INDEX(KOD, CENA_OD, CENA_DO);
ALTER TABLE products_loans ADD INDEX(CENA_OD, CENA_DO, KOD);

Notice that the order of the columns matter! If that doesn't improve performance, give us the EXPLAIN output of the query.