[MYSQL] caching bij fout gebruik group by.

[Verwijderd]

ik kwam vandaag een query tegen waarvan ik me afvraag waarom hij niet wilde cachen. ik denk dat ik het wel weet, maar vraag hier even om bevestiging van mijn gedachtes.

samengevat (was een query over stuk of 15 tabellen ;P )

SQL:

SELECT 
  foo.id,
  count(foo.id),
  bar.name
FROM foo_table foo
INNER JOIN bar_table bar ON
  foo.id = bar.foo_id
GROUP BY foo.id

mijn theorie is dat hij niet cached door dat bar.name eigenlijk een heel random iets is. het is onvoorspelbaar wat hij geeft. is dan ook een heel slechte query natuurlijk die inmiddels herschreven is.

echter dacht ik altijd dat hij enkel niet cachte bij expliciet aangeroepen niet deterministische functies als random enzo.

klopt het nou dat hij dit niet cached ivm die onvoorspelbaarheid? of moet ik het toch ergens anders zoeken?

[Verwijderd]

disclaimer: ik weet niets van MySQL ;-)

1 foo.id heeft zo te zien 1 bar.name, dus zo onvoorspelbaar zou dat toch niet moeten zijn?
En voortbordurend, waarom stop je name niet in de table foo (misschien is je voorbeeld te simpel hoor)?
En je query klopt niet, want bar.name moet of een aggregate functie krijgen, of in de group by-clause staan...

[LuCarD]

Hoe kan je zien of een query gecachet word of niet?

Is het niet beter om de query correct te maken? Ongeacht of hij cachet of niet.

Hier staat trouwens een lijstje met wanneer de cache niet kan worden gebruikt.
http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html

Haal die grote query eens door een explain.

[ Voor 41% gewijzigd door LuCarD op 30-01-2008 14:48 ]

[Moraelyn]

hmm die query klopt niet, je krijgt nu alle id's, een 1 en dan alle names. je moet foo.id uit de select halen en de group by op bar.name zetten. Id's zijn net zo goed onvoorspelbaar zoals jij dat noemt. Probeer eens een index op bar.name.

Geen idee hoe het cache mechanisme van MySQL werkt trouwens.

edit: anderen zijn me voor

[ Voor 5% gewijzigd door Moraelyn op 30-01-2008 14:57 ]

[Voutloos]

Moraelyn, nee je mag (en ts wil dat blijkbaar) gewoon je foos grouperen. Tevens zal de 2e kolom gewoon wel netjes de count zijn.

Maar goed, ik ga er eigenlijk vanuit dat er stiekem een niet cachebare functie (zie link, maar het zijn met name functies welke huidige date/time bepalen) in de query staat.

[Verwijderd]

@debbus:

1 foor kan meerdere bars hebben. voorbeeld tabel

foo

id	nogiets	watanders
1	as	fgd
2	sd	bbbb
3	df	ccc

bar:

id	foo_id	name
1	1	jan
2	1	piet
3	2	klaas
4	3	klaas
5	3	karel

dus je kan meerdere bars voor 1 foo hebben

query klopt idd niet. gaf ik zelf ook al aan. query is al herschreven maar ik wil weten waarom die chaching niet goed werkte. het si dus niet dat ik dit wil als nieuwe query. ik ben enkel nieuwsgierig naar de oorzaak.

explain geeft overigens op 1 van de tabellen een using temp en using filesort aan. alle kolommen waarop gezocht wordt zijn btw geindexed

@LuCaRd: hij is even snel met SQL_NO_CACHE als zonder(+/- 250ms) terwijl de nieuwe query wel flink verschil heeft tussen met en zonder SQL_NO_CACHE (resp 170 en 30ms)

edit: nope geen date/time functies. geen randowms. enkel select, from, inner/left join, where, group by en order by.

[ Voor 4% gewijzigd door Verwijderd op 30-01-2008 15:24 ]

[Annie]

Verwijderd schreef op woensdag 30 januari 2008 @ 14:46:
disclaimer: ik weet niets van MySQL ;-)

1 foo.id heeft zo te zien 1 bar.name, dus zo onvoorspelbaar zou dat toch niet moeten zijn?
En voortbordurend, waarom stop je name niet in de table foo (misschien is je voorbeeld te simpel hoor)?
En je query klopt niet, want bar.name moet of een aggregate functie krijgen, of in de group by-clause staan...

En waar baseer jij op dat 1 foo.id 1 bar.name heeft? Dat is niet op te maken uit het voorbeeld en ik denk zelfs dat het tegendeel waar is en er dus 1 of meer bar.name mogelijk zijn.

Dat de query niet klopt, daar was de TS ook al achter. Maar MySQL accepteert gewoon velden zonder dat ze zijn opgenomen in de group by of in een aggregate.

Moraelyn schreef op woensdag 30 januari 2008 @ 14:56:
hmm die query klopt niet, je krijgt nu alle id's, een 1 en dan alle names.

Je vergeet dat er een join wordt gedaan naar een andere table. Dus je conclusie klopt niet. Sowieso is het voorbeeld een versimpelde weergave.

Verder heb ik geen antwoord op de vraag van de TS. Ik weet niet genoeg van MySQL.

[LuCarD]

Verwijderd schreef op woensdag 30 januari 2008 @ 15:23:


query klopt idd niet. gaf ik zelf ook al aan. query is al herschreven maar ik wil weten waarom die chaching niet goed werkte. het si dus niet dat ik dit wil als nieuwe query. ik ben enkel nieuwsgierig naar de oorzaak.

explain geeft overigens op 1 van de tabellen een using temp en using filesort aan. alle kolommen waarop gezocht wordt zijn btw geindexed

A query also is not cached under these conditions:
It uses TEMPORARY tables.

[Verwijderd]

@LuCaRd... hey dat is hem inderdaad... zo heb ik weer wat geleerd. die link in ieder geval ook even opgeslagen..

tnx!! nu kan ik weer rustig slapen.

[LuCarD]

Als je query kloppend maakt dan heb je een grote kans dat de "using temporary" verdwijnt.

Heb je ook een order by in de query?

[Voutloos]

LuCarD schreef op woensdag 30 januari 2008 @ 15:34:
heb je ook een order by in de query?

Vraag dan gewoon meteen om een complete explain

[AtleX]

LuCarD schreef op woensdag 30 januari 2008 @ 15:25:
[...]


[...]

Met betrekking tot je 2e quote moet ik je ongelijk geven. Ik weet dat het in de MySQL documentatie staat maar het blijkt dat MySQL 5 (iig 5.0.45) prima queries waarvoor een temporary table nodig is kan cachen. Ik heb hier namelijk een niet geoptimaliseerde query waar EXPLAIN het volgende over meld:

+--------+-------+---------------+---------+---------+---------------+------+---------------------------------+
| table  | type  | possible_keys | key     | key_len | ref           | rows | Extra                           |
+--------+-------+---------------+---------+---------+---------------+------+---------------------------------+
| pages  | index | PRIMARY       | PRIMARY |       2 | NULL          |  140 | Using temporary; Using filesort |
| visits | ref   | page_id       | page_id |       2 | pages.page_id |   17 | Using where                     |
+--------+-------+---------------+---------+---------+---------------+------+---------------------------------+

De query cache is gereset:

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW status LIKE '%qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 268426376 |
| Qcache_hits             | 24        |
| Qcache_inserts          | 24        |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 1310      |
| Qcache_queries_in_cache | 0         |
| Qcache_total_blocks     | 1         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql>

Vervolgens voer ik de query 2x uit en kijk ik weer naar de query cache:

mysql>(query)
(output)
71 rows in set (0.02 sec)

mysql>(query)
(output)
71 rows in set (0.00 sec)

mysql> SHOW status LIKE '%qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 268421544 |
| Qcache_hits             | 26        |
| Qcache_inserts          | 26        |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 1312      |
| Qcache_queries_in_cache | 1         |
| Qcache_total_blocks     | 5         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql>

Door middel van profiling is het duidelijker te zien:

mysql> SET profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql>(query)
(output)
71 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| (initialization)               | 0.0000037 |
| checking query cache for query | 0.0000082 |
| checking privileges on cached  | 0.0000067 |
| sending cached result to clien | 0.0000347 |
| logging slow query             | 0.0000027 |
+--------------------------------+-----------+
5 rows in set (0.00 sec)

mysql>

Zo te zien worden queries die gebruik maken van een temporary table toch gecached.

[ Voor 11% gewijzigd door AtleX op 12-02-2008 19:23 ]

[Voutloos]

Nee, het woord temporary wordt vaker gebruikt en daar is de betekenis dus ambigue. Queries met 'using temporary' in het execution plan kunnen prima gecached worden, het gaat om queries over tables welke gemaakt zijn met CREATE TEMPORARY, welke niet gecached kunnen worden.

Gelukkig maar, want queries met 'Using temporary' (zeker icm met filesort) zijn juist de queries welke gemiddeld genomen het cachen zeker waard zijn. Een TEMPORARY Table is vluchtig en hoort maar bij 1 connectie en kan daarom gewoon niet gecached worden.

[ Voor 36% gewijzigd door Voutloos op 13-02-2008 08:22 ]

[Verwijderd]

Verwijderd schreef op woensdag 30 januari 2008 @ 14:31:
mijn theorie is dat hij niet cached door dat bar.name eigenlijk een heel random iets is. het is onvoorspelbaar wat hij geeft.

Name kan van 1 foo.id verschillende namen bevatten. Mysql gokt dit, en zal het daarom niet cachen. In een strictere db zal deze query gewoon weg niet werken. (geloof ik)

Ik denk dat je dus gelijk hebt.

[AtleX]

Voutloos schreef op woensdag 13 februari 2008 @ 08:17:
Nee, het woord temporary wordt vaker gebruikt en daar is de betekenis dus ambigue. Queries met 'using temporary' in het execution plan kunnen prima gecached worden, het gaat om queries over tables welke gemaakt zijn met CREATE TEMPORARY, welke niet gecached kunnen worden.

Gelukkig maar, want queries met 'Using temporary' (zeker icm met filesort) zijn juist de queries welke gemiddeld genomen het cachen zeker waard zijn. Een TEMPORARY Table is vluchtig en hoort maar bij 1 connectie en kan daarom gewoon niet gecached worden.

Uhm ja, stiekem wist ik dat wel. Interpretatiefoutje.