MySQL Forums
Forum List  »  Optimizer & Parser

Variable where index optimizing
Posted by: alex jordanov
Date: June 29, 2007 04:37AM

This will be a long post so here we go.

I have this table(removed most of the columns as they weren't relevant):
Quote

CREATE TABLE `Tmpinfo` (
`AID` mediumint(8) unsigned NOT NULL default '0',
`Fname` varchar(20) default NULL,
`Lname` varchar(20) default NULL,
`Email` varchar(55) default NULL,
`Lcity` enum(<list of citys>) default NULL,
`Fcity` smallint(5) unsigned default NULL,
`Country` enum(<list of countries>) default NULL,
`Birthday` date default NULL,
`Gender` enum('men','women') default NULL,
`Opposite` enum('men','women','no matter') default NULL,
`Created` date default NULL,
`Accessed` datetime default NULL,
`Modified` date default NULL,
`primaryPic` tinyint(3) NOT NULL default '0',
`Votes` mediumint(8) default '1',
`Sumrate` mediumint(8) default '10',
`Rating` float(4,2) default '10.00',
`Anonymreg` enum('Да','Не') default 'Не',
`hasPic` enum('yes','no') NOT NULL default 'yes',
UNIQUE KEY `AID` (`AID`),
KEY `create_inx` (`Created`),
KEY `gab_inx` (`Gender`,`Accessed`,`Birthday`),
KEY `gcpab_inx` (`Gender`,`Lcity`,`hasPic`,`Accessed`,`Birthday`),
KEY `gcab_inx` (`Gender`,`Lcity`,`Accessed`,`Birthday`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Quote

+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Tmpinfo | 0 | AID | 1 | AID | A | 100000 | NULL | NULL | | BTREE | |
| Tmpinfo | 1 | create_inx | 1 | Created | A | 2000 | NULL | NULL | YES | BTREE | |
| Tmpinfo | 1 | gab_inx | 1 | Gender | A | 5 | NULL | NULL | YES | BTREE | |
| Tmpinfo | 1 | gab_inx | 2 | Accessed | A | 33333 | NULL | NULL | YES | BTREE | |
| Tmpinfo | 1 | gab_inx | 3 | Birthday | A | 100000 | NULL | NULL | YES | BTREE | |
| Tmpinfo | 1 | gcpab_inx | 1 | Gender | A | 5 | NULL | NULL | YES | BTREE | |
| Tmpinfo | 1 | gcpab_inx | 2 | Lcity | A | 283 | NULL | NULL | YES | BTREE | |
| Tmpinfo | 1 | gcpab_inx | 3 | hasPic | A | 552 | NULL | NULL | | BTREE | |
| Tmpinfo | 1 | gcpab_inx | 4 | Accessed | A | 100000 | NULL | NULL | YES | BTREE | |
| Tmpinfo | 1 | gcpab_inx | 5 | Birthday | A | 100000 | NULL | NULL | YES | BTREE | |
| Tmpinfo | 1 | gcab_inx | 1 | Gender | A | 5 | NULL | NULL | YES | BTREE | disabled |
| Tmpinfo | 1 | gcab_inx | 2 | Lcity | A | 283 | NULL | NULL | YES | BTREE | disabled |
| Tmpinfo | 1 | gcab_inx | 3 | Accessed | A | 100000 | NULL | NULL | YES | BTREE | disabled |
| Tmpinfo | 1 | gcab_inx | 4 | Birthday | A | 100000 | NULL | NULL | YES | BTREE | disabled |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
the table has 100k rows
and in this table i have to run queries like this:
Quote

EXPLAIN SELECT * FROM Tmpinfo
WHERE gender = 2 AND lcity = 2
AND Birthday <= "1989-06-29" AND Birthday > "1969-06-29"
AND Accessed > "2000-01-01"
ORDER BY Accessed desc LIMIT 0 , 30
Explain of this query returns:
Quote

+----+-------------+---------+-------+----------------------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+----------------------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | Tmpinfo | range | gab_inx,gcpab_inx,gcab_inx | gcab_inx | 13 | NULL | 2727 | Using where |
+----+-------------+---------+-------+----------------------------+----------+---------+------+------+-------------+
Now this obviously is good due to the indexes, but i might have or don't have any of these fields:`Gender`,`Lcity`,`hasPic`,`Accessed`,`Birthday`, and sorting can be done on Accessef,Birthday or AID columns.When this happens mysql starts using filesort, and things get ugly.
From what i know about indexing it turns out that i have to make an index for almost every case of where conditions and sorting order in order to always use the index. The problem is mainly the sorting as it is always there(almost no queries are without sorting - that is the complex queries, not simple ones type: select * from table where id=xx where id is a key), so when the index is not correct the sorting starts using filesort.
There is the problem of Opposite column too. It's in the queries very often too(almost as often as Gender - which is why it is on first place in the index). Opposite is in the form AND Opposite IN (1,3). No matter how i build the index it never uses the index for selecting it:
Quote

EXPLAIN SELECT * FROM Tmpinfo
WHERE Gender = 2 AND Opposite != 2 AND Lcity = 1 AND Accessed > "2000-01-01"
ORDER BY Accessed DESC LIMIT 0 , 30;
Quote

+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | Tmpinfo | ref | golb_inx | golb_inx | 2 | const | 9312 | Using where; Using filesort |
+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+


So in summary my questions are how can i avoid building lots of indexes, that will do the job. And how can i include Opposite in the index so that it uses the index in the query.

Mysql version is 4.1.22 on Gentoo linux.
Thank you

Options: ReplyQuote


Subject
Views
Written By
Posted
Variable where index optimizing
2841
June 29, 2007 04:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.