[MySQL] Query optimilisatie issue met limit

[Olaf van der Spek]

Het probleem is dat MySQL 5.0 alle 300k rows uit de chat1 tabel van disk lijkt te lezen en dat duurt extreem lang. Weet iemand hoe ik deze query kan verbeteren zodat het wel in een redelijke tijd gebeurd?

SQL:

mysql> describe xwi_chat_to;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| chat_id | int(11) | NO   | MUL |         |       | 
| nid     | int(11) | NO   | MUL |         |       | 
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe xwi_chat1;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| chat_id | int(11)      | NO   | PRI | NULL    | auto_increment | 
| from    | int(11)      | NO   | MUL |         |                | 
| msg     | varchar(255) | NO   |     |         |                | 
| time    | int(11)      | NO   |     |         |                | 
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> explain select c.* from xwi_chat1 c inner join xwi_chat_to using (chat_id) where xwi_chat_to.nid in (122) order by chat_id desc limit 1000;      
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------+--------+---------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                     | rows   | Extra                           |
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------+--------+---------------------------------+
|  1 | SIMPLE      | xwi_chat_to | ref    | chat_id,nid   | nid     | 4       | const                   | 298612 | Using temporary; Using filesort | 
|  1 | SIMPLE      | c           | eq_ref | PRIMARY       | PRIMARY | 4       | xcc.xwi_chat_to.chat_id |      1 |                                 | 
+----+-------------+-------------+--------+---------------+---------+---------+-------------------------+--------+---------------------------------+
2 rows in set (0.00 sec)

mysql> select chat_id from xwi_chat_to where nid in (122) order by chat_id desc limit 1000;
1000 rows in set (3.74 sec)

[The Eagle]

Heb je uberhaupt indexes aangemaakt op die tabel Anders moet ie idd een full table search doen, en dat wil je niet

Heb @work laatst een identiek probleem gehad...join van twee tabellen op non-key values. Da's op zich niet heel spannend, behalve als het een operationele rapportage betreft en ie voor iedere regel die in tabel A vind heel tabel B door moet zoeken omdat ie niet op keyvalues kan zoeken. En laat tabel B nou dik 3mln rijen bevatten
Processing time zonder de index: 8+ uur. Met: 10 sec. Serieus. Oracle 9i op een Sun Solaris bak, productie ERP-systeem

[ Voor 5% gewijzigd door The Eagle op 04-02-2007 23:54 ]

[Olaf van der Spek]

The Eagle schreef op zondag 04 februari 2007 @ 23:53:
Heb je uberhaupt indexes aangemaakt op die tabel Anders moet ie idd een full table search doen, en dat wil je niet

Ja, zie de describes en explain.

[Voutloos]

Je kan daaraan toch niet goed zien wat de indexes precies zijn, want een index als nid, chat_id is wellicht beter als alleen nid. Verder mag je ook wel zeggen welke storage engine gebruikt wordt.

Ook zou ik in het geval van 1 nid als where clause wi_chat_to.nid = 122 doen.

[Olaf van der Spek]

Voutloos schreef op maandag 05 februari 2007 @ 00:04:
Je kan daaraan toch niet goed zien wat de indexes precies zijn, want een index als nid, chat_id is wellicht beter als alleen nid. Verder mag je ook wel zeggen welke storage engine gebruikt wordt.

Ook zou ik in het geval van 1 nid als where clause wi_chat_to.nid = 122 doen.

Er kunnen ook meerdere nids zijn.

SQL:

CREATE TABLE `xwi_chat_to` (
  `chat_id` int(11) NOT NULL,
  `nid` int(11) NOT NULL,
  KEY `chat_id` (`chat_id`),
  KEY `nid` (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `xwi_chat1` (
  `chat_id` int(11) NOT NULL auto_increment,
  `from` int(11) NOT NULL,
  `msg` varchar(255) NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY  (`chat_id`),
  KEY `from` (`from`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

[GlowMouse]

Weet je dat MySQL maximaal één index per tabel gebruikt bij een query? Nu gebruikt hij de index nid van xwi_chat_to, zodat hij niet meer de index chat_id kan gebruiken om te sorteren. Je kunt beter een index definieren over meerdere velden.

[Voutloos]

Precies, en probeer eens zoals ik al zei nid, chat_id (in deze volgorde!)

btw GlowMouse, MySQL 5.nogwat heeft wel index intersect en index union als nieuw hippe features. Imo misschien wel de belangrijkste wijziging.

[ Voor 50% gewijzigd door Voutloos op 05-02-2007 00:24 ]

[Olaf van der Spek]

GlowMouse schreef op maandag 05 februari 2007 @ 00:15:
Weet je dat MySQL maximaal één index per tabel gebruikt bij een query? Nu gebruikt hij de index nid van xwi_chat_to, zodat hij niet meer de index chat_id kan gebruiken om te sorteren.

Maar dat kan toch zoiezo niet (zelfs in theorie)? Met een index over meerdere velden zou het misschien wel kunnen, maar volgens mij kan het in theorie gewoon zonder, dus met de huidige indexes.

[ Voor 17% gewijzigd door Olaf van der Spek op 05-02-2007 00:24 ]