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 [mailto:[EMAIL PROTECTED] > 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]
