There are some unnecessary indexes though. These include id_2 and 1st. This is because id_3 is an index on (id,parent,date) and id_2 is and index on (id,parent). The way mysql uses indexes, id_3 will work for (id), (id,parent), and (id,parent,date). The same is true of the '1st' index. This is covered by 1st_2 or 1st_3.
--- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -----Original Message----- From: Tom Crimmins Sent: Monday, January 03, 2005 2:49 PM To: Donny Simonton; 'Fredrik Carlsson' Cc: mysql@lists.mysql.com Subject: RE: Max connections being used every 10-12 day. [snip] I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 [/snip] id_2 and id_3 are composite indexes. As the explain shows, mysql is using id_3 because it is the most specific to the query. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -----Original Message----- From: Donny Simonton Sent: Monday, January 03, 2005 1:12 PM To: 'Fredrik Carlsson' Cc: mysql@lists.mysql.com Subject: RE: Max connections being used every 10-12 day. Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny > -----Original Message----- > From: Fredrik Carlsson > Sent: Monday, January 03, 2005 11:08 AM > To: Donny Simonton > Cc: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > mysql> describe art; > +---------+--------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +---------+--------------+------+-----+---------+----------------+ > | id | int(11) | | PRI | NULL | auto_increment | > | parent | int(11) | YES | MUL | NULL | | > | bodyid | int(11) | YES | | NULL | | > | lst | varchar(80) | YES | MUL | NULL | | > | mf | varchar(80) | YES | | NULL | | > | mt | varchar(80) | YES | | NULL | | > | subc | varchar(200) | YES | MUL | NULL | | > | sdate | varchar(45) | YES | | NULL | | > | batch | varchar(80) | YES | MUL | NULL | | > | mgid | varchar(90) | YES | | NULL | | > | date | datetime | YES | MUL | NULL | | > +---------+--------------+------+-----+---------+----------------+ > 11 rows in set (0.12 sec) > > > mysql> show index from art; > +------------+------------+----------+--------------+-------------+------- > ----+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment > | > +------------+------------+----------+--------------+-------------+------- > ----+-------------+----------+--------+------+------------+---------+ > | art | 0 | PRIMARY | 1 | id | A > | 405011 | NULL | NULL | | BTREE | | > | art | 1 | id | 1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | date | 1 | date | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst | 1 | lst | > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | id_2 | 1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | id_2 | 2 | parent | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 | 1 | lst | > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 | 2 | parent | > A | 57858 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 | 3 | date | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | batch | 1 | batch | > A | 141 | NULL | NULL | YES | BTREE | > | > | art | 1 | batch | 2 | lst | > A | 1177 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_3 | 1 | lst | > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_3 | 2 | parent | > A | 57858 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_3 | 3 | batch | > A | 67501 | NULL | NULL | YES | BTREE | > | > | art | 1 | id_3 | 1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | id_3 | 2 | parent | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | id_3 | 3 | date | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | parent | 1 | parent | > A | 57858 | NULL | NULL | YES | BTREE | > | > | art | 1 | subc | 1 | subc | A > | 40501 | NULL | NULL | YES | FULLTEXT | | > +------------+------------+----------+--------------+-------------+------- > ----+-------------+----------+--------+------+------------+---------+ > 19 rows in set (0.04 sec) > > > The query: > > Select A.id, A.parent, B.id, B.parent > from art A inner join art B using (id) > order by A.date; > > Would return several hundered thousend answers so i added a where > option, i dont know if this was what you had in mind. > The way i used your query it should be the same thing as: > Select A.id, A.parent from art A where A.id=60 or A.parent=60 order by > A.date; > > mysql> explain Select A.id, A.parent from art A inner join art B using > (id) where A.id=60 or A.parent=60 order by A.date; > +-------+--------+----------------------------------+---------+---------+- > ------------------+------+------------------------------------------+ > | table | type | possible_keys | key | key_len > | ref | rows | Extra | > +-------+--------+----------------------------------+---------+---------+- > ------------------+------+------------------------------------------+ > | A | index | PRIMARY,id,id_2,id_3,parent | id_3 | 18 | > NULL | 37 | Using where; Using index; Using filesort | > | B | eq_ref | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | > testdb.A.id | 1 | Using index | > +-------+--------+----------------------------------+---------+---------+- > ------------------+------+------------------------------------------+ > 2 rows in set (0.00 sec) > > The table and index structure are from the production database but the > query was executed on my test database (same structure but not the same > amount of rows) > > // Fredrik > > Donny Simonton wrote: > > >The inner join should ALWAYS return a faster result than the union, if > you > >have the indexes correctly. > > > >Can you send me the explain of the inner join version and also the full > >table structure and indexes on the table? > > > >This should be fairly easy to solve. > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
