Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Update Piwik log_visit table without using index??? #2254

Closed
anonymous-matomo-user opened this issue Apr 2, 2011 · 7 comments
Closed

Update Piwik log_visit table without using index??? #2254

anonymous-matomo-user opened this issue Apr 2, 2011 · 7 comments
Assignees
Labels
Bug For errors / faults / flaws / inconsistencies etc. worksforme The issue cannot be reproduced and things work as intended.
Milestone

Comments

@anonymous-matomo-user
Copy link

UPDATE piwik_log_visit
SET visit_total_actions = visit_total_actions + 1
, visit_exit_idaction_url = '873038'
, visit_exit_idaction_name = '16'
, visit_last_action_time = '2011-04-02 09:27:12'
, visit_total_time = '1347'
WHERE idsite = '53' AND idvisit = '2570066' AND idvisitor = '?^V^C??Q\0?';

idvisit is primary key, and why you use "idsite" and "idvisitor" ???

@anonymous-matomo-user
Copy link
Author

That really confused me, since idvisit is the primary key of piwik_log_visit,
why should you use the two extra column to query...
And when I explained the query above, like "select * from piwik_log_visit WHERE idsite = '53' AND idvisit = '2570066' AND idvisitor = '?VC??Q\0?';om",
I notice there is no key have been used for this query, So,
could you tell me why?

@robocoder
Copy link
Contributor

yuanlihao: Yes, idivisit is unique. The idsite and idvisitor in that WHERE clause are effectively redundant.

leoyan88: I think there's something wrong with your EXPLAIN or database schema. When I run EXPLAIN, it shows the PRIMARY key is used.

@robocoder
Copy link
Contributor

BTW none of you indicated what version of Piwik you're using or why you're looking at the SQL, but you may be interested in [4204].

@mattab
Copy link
Member

mattab commented Apr 2, 2011

I agree the other fields are not necessary, but they don't harm neither.

@anonymous-matomo-user
Copy link
Author

Thanks for you guys, and sorry for my carelessness.
Per vipsoft's request, I checked the version of my database, it's
mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1
and we're using piwik1.2 right this moment.

I double checked what vipsoft said, but I still told I didn't use any kind of keys, something wrong with MySQL or any other reasons?
mysql> explain select * from piwik_log_visit WHERE idsite = '53' AND idvisit = '2570066' AND idvisitor = '?VC??Q\0?';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.04 sec)

mysql> explain select * from piwik_log_visit WHERE idvisit = '2570066';
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | piwik_log_visit | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+

@anonymous-matomo-user
Copy link
Author

Thanks for you guys, and sorry for my carelessness.

Per vipsoft's request, I checked the version of my database, it's mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64) using readline 6.1 and we're using piwik1.2 right this moment.

I have double checked what vipsoft said, but I still be told I didn't use any kind of keys, something wrong with MySQL or any other reasons?
mysql> explain select * from piwik_log_visit WHERE idsite = '53' AND idvisit = '2570066' AND idvisitor = '?VC??Q\0?'; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.04 sec)

mysql> explain select * from piwik_log_visit WHERE idvisit = '2570066'; +----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | piwik_log_visit | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------+

Any answers would be appreciated, thanks in advance.

@robocoder
Copy link
Contributor

leoyuan88: we bind the parameters in the SQL statement, so we can use binary data for idvisitor; you can't do the same using the mysql client -- try using hex()/unhex() on the idvisitor field

This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Bug For errors / faults / flaws / inconsistencies etc. worksforme The issue cannot be reproduced and things work as intended.
Projects
None yet
Development

No branches or pull requests

3 participants