Variable where index optimizing
Posted by: alex jordanov
Date: June 29, 2007 04:37AM
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):
and in this table i have to run queries like this:
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:
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
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;
the table has 100k rowsQuote
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| 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 |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
and in this table i have to run queries like this:
Explain of this query returns: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
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.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 |
+----+-------------+---------+-------+----------------------------+----------+---------+------+------+-------------+
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
Subject
Views
Written By
Posted
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.