[MySQL] Your query requires a full tablescan

[frickY]

Ik heb sinds kort in MySQL aangezet dat hij alle meldingen toont, om zo tot op het bot te debuggen en alles te optimaliseren.

Nu geeft 1 van mijn scripts (een fotoboek) de volgende melding;

Warning: mysql_query() [http://www.mysql.com/doc]: Your query requires a full tablescan (table groepen, 2 rows affected). Use EXPLAIN to optimize your query. in \fotoboek\index.php on line 242

Deze melding betekend over het algemeen dat de indexes niet helemaal lekker zitten, en je query dus niet optimaal is. Maar..

Op regel 242 staat de volgende query;

code:

SELECT `groep_ID`, `groep_naam` FROM `groepen` ORDER BY `groep_order`

De tabel 'groepen' ziet er als volgt uit:

Veld	Type
groep_ID	int(10)
groep_naam	varchar(50)
groep_order	tinyint(3)

Met een PRIMARY KEY op 'groep_ID' en een UNIQUE-index op 'groep_order'.

Een EXPLAIN geeft dit terug;

table	type	possible_keys	key	key_len	ref	rows	Extra
groepen	ALL	NULL	NULL	NULL	NULL	2	Using filesort

Als ik een ANALYZE op de tabel loslaat krijg ik een "Table is already up to date".

Dus de vraag... waar zeikt MySQL over?!

Alles wat ik over deze melding kan vinden gaat over tabellen zonder indexen, of inet-slimmer joins. Maar er is totaal niets boeiends aan deze tabel. Wat is het probleem? Hij bevat op het moment ook nog maar 2 records.

We hebben het hier over MySQL 4.0.21-nt op WinXP Pro SP2, werkend met PHP Version 5.0.1 en Apache 2.0

[ Voor 20% gewijzigd door frickY op 14-10-2004 22:48 ]

[--MeAngry--]

Ik mis de kolom 'groep_order' een beetje

[frickY]

ohw? Ik kan hem toch heel goed zien.
3 kolommen; groep_ID, groep_naam, groep_order

Of heb ik teveel koffie op

[Olaf van der Spek]

frickY schreef op 14 oktober 2004 @ 22:34:
Als ik een ANALYZE op de tabel loslaat krijg ik een "Table is already up to date".

Dus de vraag... waar zeikt MySQL over?!

ANALYZE is niet EXPLAIN.
En je hebt geen WHERE, dus is het toch logisch dat je alle rows select (en dus een full table scan doet)?

[Verwijderd]

Het probleem zit hem volgens mij in dat je group_id en group_naam selecteerd, en wil orderen op iets wat je niet selecteerd. (group_order).

[ Voor 10% gewijzigd door Verwijderd op 14-10-2004 22:52 ]

[mr_taipan]

frickY schreef op 14 oktober 2004 @ 22:41:
ohw? Ik kan hem toch heel goed zien.
3 kolommen; groep_ID, groep_naam, groep_order

Of heb ik teveel koffie op

moet wel

groep_order staat niet in de select.
Mysql zal daarom zelf de table nog een keer langslopen als hij bij de order by komt

edit

crap iemand was net eerder

[ Voor 6% gewijzigd door mr_taipan op 14-10-2004 22:53 ]

[frickY]

Ahh. Ik begreep je gewoon verkeerd.
Dus door 'groep_order' mee te selecten zou dit opgelost moeten zijn?

Lijkt me inederdaad slimme opmerkingen. Hij moet nu groep_order ook 'selecten' om te weten hoe hij moet sorteren...

>>

Gelukkig maar. Zou mezelf erg dom voelen als dit zou kloppen.
Ook als ik 'groep_order' mee select hou ik dit probleem.

Dat ik alle records selecteer (= geen WHERE) betekend toch niet dat ik een 'full tablescan' doe?

@OlafvdSpek
Dat weet ik. Daarom staat boven het resultaat van ANALYZE het resultaat van de EXPLAIN. Gezien de MySQL docs aanraden een analyze te doen heb ik dat resuktaat er ook bijgezet.

Mocht iemand dezelfde MySQL-optie ('mysql.trace' in PHP.ini) aan hebben staan en het willen proberen. Hier de tabel;

code:

CREATE TABLE `groepen` (
  `groep_ID` int(10) unsigned NOT NULL auto_increment,
  `groep_naam` varchar(50) NOT NULL default '',
  `groep_order` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`groep_ID`),
  UNIQUE KEY `groep_order` (`groep_order`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

[ Voor 58% gewijzigd door frickY op 14-10-2004 23:06 ]

[Verwijderd]

Doet MySQL dan vervolgens een index search bij 2 records ? Ik hoop dat MySQL weet dat een tablescan sneller is onder de 1000 records, of wanneer meer dan circa 6% van de records tot de resultset gaat behoren.

[frickY]

Das ook nog een goeie ja

En de mensen die zeggen; "duh dat ie n tablescan doet, je hebt geen WHERE". Dit is een mysql notice/warning. Blijkbaar is er dus iets mis. En volgens mij moet je prika query's kunnen draaien zonder WHERE. Of moet ik dan net als phpmyadmin doet 'WHERE 1=1' gebruiken?

[Olaf van der Spek]

frickY schreef op 16 oktober 2004 @ 11:11:
Das ook nog een goeie ja

En de mensen die zeggen; "duh dat ie n tablescan doet, je hebt geen WHERE". Dit is een mysql notice/warning. Blijkbaar is er dus iets mis.

De vraag is dus of er iets mis is met je query of met de MySQL notices/warnings.

[justmental]

Je mag hopen dat ie een full tablescan doet als je alle 2 de records uit een tabel selecteert.
Waarom MySQL dat een waarschuwing waard vindt is me een raadsel.

[ACM]

Als je gewoon alle groepen moet hebben, dan is de enige manier om die op te vragen door ze allemaal op te halen. En dat is alleen met een table scan mogelijk, dus het is een zinloze melding in dit geval...

[ACM]

justmental schreef op 16 oktober 2004 @ 12:32:
Je mag hopen dat ie een full tablescan doet als je alle 2 de records uit een tabel selecteert.
Waarom MySQL dat een waarschuwing waard vindt is me een raadsel.

Helaas:

code:

mysql> create table indextest (id integer, key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into indextest values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> analyze table indextest;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| indextest | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> optimize table indextest;
+-------------------+----------+----------+----------+
| Table             | Op       | Msg_type | Msg_text |
+-------------------+----------+----------+----------+
| indextest | optimize | status   | OK       |
+-------------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> explain select * from indextest where id = 1;
+-----------+------+---------------+------+---------+-------+------+--------------------------+
| table     | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+-----------+------+---------------+------+---------+-------+------+--------------------------+
| indextest | ref  | id            | id   |       5 | const |    1 | Using where; Using index |
+-----------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.03 sec)

[justmental]

Dat is 1 uit 3 toch

[ACM]

Ja, een index scan voor 1 uit 3 integers. Eigenlijk quote ik de verkeerde reactie ook, ik had beter die van bloog kunnen quoten

[ Voor 53% gewijzigd door ACM op 16-10-2004 12:49 ]

[.oisyn]

En dat de kolom waarop je orderd ook in de select moet staan is echt onzin, dat is alleen het geval bij group by

Zie trouwens ook http://dev.mysql.com/doc/mysql/en/ORDER_BY_optimization.html

[ Voor 27% gewijzigd door .oisyn op 16-10-2004 14:30 ]

[frickY]

Tgaat nu allemaal n beetje boven mijn sql-petje.

Maar het probleem is dus dat MySQL de index op de kolom groep_order niet gebruikt, waardoor een full tablescan nodig is om de records te kunnen sorteren?
En mijn doel nu zou dus moeten zijn uitzoeken waarom hij de index niet gebruikt?

[Olaf van der Spek]

frickY schreef op 17 oktober 2004 @ 17:20:
En mijn doel nu zou dus moeten zijn uitzoeken waarom hij de index niet gebruikt?

Ik zou het gewoon vergeten tot het echt nodig is.
Is de query inderdaad (te langzaam) nu?
Of ben je al aan het optimaliseren terwijl dat niet nodig is?

[justmental]

frickY schreef op 17 oktober 2004 @ 17:20:
Tgaat nu allemaal n beetje boven mijn sql-petje.

Maar het probleem is dus dat MySQL de index op de kolom groep_order niet gebruikt, waardoor een full tablescan nodig is om de records te kunnen sorteren?
En mijn doel nu zou dus moeten zijn uitzoeken waarom hij de index niet gebruikt?

Een full tablescan is het meest efficiente in jouw geval, je zou je mogelijk zorgen kunnen maken als de index wel gebruikt werd.

[frickY]

Ik heb totaal geen problemen met die query en zn performance.. maar MySQL blijft die notice terug spugen. En ik wil in principe alles 100% geoptimaliseerd hebben, ookal is dit niet echt nodig. Gaat me gewoon om het idee. Daarom staan alle notices ed allemaal aan..
Ik neem aan dat er een methode is om deze query uit te voeren zonder dat MySQL het nodig vind een notice te geven...

[ Voor 3% gewijzigd door frickY op 17-10-2004 19:31 ]

[Domokoen]

frickY schreef op 17 oktober 2004 @ 19:28:En ik wil in principe alles 100% geoptimaliseerd hebben, ookal is dit niet echt nodig. Gaat me gewoon om het idee. Daarom staan alle notices ed allemaal aan..
Ik neem aan dat er een methode is om deze query uit te voeren zonder dat MySQL het nodig vind een notice te geven...

Tsja... je optimaliseert de boel nu waarschijnlijk meer door die extra notices uit te zetten, want MySQL draait nu extra optimalisatie-checks om die notices te maken. Volgens mij is het idee van die notices dat je ze tijdlelijk aanzet om bottlenecks op te sporen en/of te optimaliseren, niet om deze continu te gebruiken.
Dus inderdaad, het is mogelijk om deze query uit te voeren zonder dat MySQL het nodig vind om een notice te geven... door de notices uit te zetten.

[Verwijderd]

wat als je

code:

SELECT `groep_ID`, `groep_naam` FROM `groepen` WHERE 1 ORDER BY `groep_order`

doet?

[frickY]

Mr.Chinchilla schreef op 17 oktober 2004 @ 20:05:
[...]

Tsja... je optimaliseert de boel nu waarschijnlijk meer door die extra notices uit te zetten, want MySQL draait nu extra optimalisatie-checks om die notices te maken. Volgens mij is het idee van die notices dat je ze tijdlelijk aanzet om bottlenecks op te sporen en/of te optimaliseren, niet om deze continu te gebruiken.
Dus inderdaad, het is mogelijk om deze query uit te voeren zonder dat MySQL het nodig vind om een notice te geven... door de notices uit te zetten.

Dus jij verstaat onder PHP debuggen ook;

PHP:

ini_set("display_errors", 0);
error_reporting(0);

?
MySQL zegt letterlijk "Use EXPLAIN to optimize your query. in D:\WWW\fotoboek\index.php on line 242". Blijkbaar valt er dus iets te optimaliseren. En ik wil dus graag weten wat dan wel. Want ik zou het niet weten.

Ik ga t ff proberen Lusch

edit:
Hetzelfde

Als ik deze query overigens vervang met een query welke alleen de groepen selecteerd welke onderwerpen bevatten, welke op hun beurt weer fotos bevatten, krijg ik geen notice. Nu zal ik uiteindelijk een dergelijke query gaan gebruiken, en dus geen 'last' meer hebben van deze melding. Maar ik wil nu toch graag weten wat er volgens mysql nog geoptimaliseerd kan worden...

code:

SELECT
  groepen.`groep_ID`, groepen.`groep_naam`
FROM
  `{$DB_vars['table_groepen']}`,
  `{$DB_vars['table_onderwerpen']}`,
  `{$DB_vars['table_fotos']}`
WHERE
  groepen.`groep_ID` = onderwerpen.`onderwerp_ID` AND
  onderwerpen.`onderwerp_ID` = fotos.`onderwerp_ID`
GROUP BY
  groepen.`groep_ID`
ORDER BY
  groepen.`groep_order`

Mn indexes ed. lijken me dus wel in orde, anders zou hij bij deze query helemaal over performance beginnen te janken.

[ Voor 62% gewijzigd door frickY op 17-10-2004 22:05 ]