Linsdau: Rumery: yeah, let focus on ordering problem :
Rumery: Toomus, how do you decide that “user is online” ? and where
Clyatt: Rumery: last seen 1 min
Rumery: Oh, I meant – sql or app code
Clyatt: Toomus: That translates to last_seen_at NOW – INTERVAL 1 MINUTE
Donatello: This is exmple query, which returns online friends of some user http://pastie.org/10418677
Clyatt: Toomus: Put it in WHERE clause and you will see these users only. Put it in ORDER BY clause only and you will get all users, but you can sort these online first
Clyatt: Toomus: Sorry it is not
Clyatt: Toomus: . ORDER BY last_seen_at NOW – INTERVAL 1 MINUTE, name
Clyatt: Toomus: You definitely need to get rid of that serialized array and use separate table for friends
Sprecher: Because I can’t do ‘join’?
Clyatt: Toomus: Because of several reasons most of them explained at the article Rumery told you about
Miyake: Toomus: http://gtowey.blogspot.com/2009/12/how-to-fix-comma-separated-list-of-doom.html
Ekker: Salle: suppose I can’t do this now :
Clyatt: Toomus: Then you are doomed :
Blonder: I have an old MySQL 5.0.95 and one of my tables reached autoincr max value of the INT type. There’s only one row with just the max int value. I cannot set the autoincrement to 1 again. Is this the correct behavior?
Clovis: Which would mean that the autoincrement is computed as max_current_value + 1. Correct?
Mckeon: Salle: OK, sorry, I realize that friend relation is held in another table. This serialized array is only some kind of cache ;
Oganyan: Salle: it is done by ‘marks’ table, where user can mark another user as friend
Hauptmann: Salle: but I can’t see how this knowledge would help me ;
Demiel: How can i check the progress of a mysqlcheck?
Demiel: It’s been stuck now for at least 10 minutes
Clyatt: Toomus: Why? You want to select friends for some user and order them
Clyatt: Toomus: This marks table makes it much easier
Clyatt: Zamba: Next time run it with -v
Demiel: Salle: doing it now, but it’s not exactly giving any more info
Clyatt: Zamba: If you have some huge table it can take a lot of time to check it
Demiel: Warning : InnoDB: The B-tree of index “GEN_CLUST_INDEX” is corrupted.
Demiel: But it looks like it said “OK” nevertheless
Demiel: Http://pastebin.com/dzGfESLT
Demiel: So i guess zabbix.history is the table that’s corrupted?
Raffone: I want to know how this could be improved upon. It seems to be simple, but I’m clueless. http://pastie.org/private/geemclti2kp8fcdh8ribq
Rumery: Betterton, can products.id be null? If not then you might do “count1” instead, but afaik MySQL is bright enough to optimize this anyway so the difference will be minimal if anny
Clyatt: Rumery: I wouldn’t be surprised if it is already optimized as COUNT1
Clyatt: Products.id looks like primary key anyway
Rumery: Salle, yes, I think so too
Demiel: Salle: any idea how i should proceed?
Demiel: Salle: check my paste, please
Demiel: Salle: mysqlcheck found one corrupted table
Demiel: Salle: is the table fixed? should i run some scripts to fix it?
Clyatt: Zamba: Do you want me to read the MySQL Manual for you?
Ventrice: Zamba: See http://dev.mysql.com/doc/refman/5.6/en/repair-table.html
Clyatt: Zamba: See what it says about InnoDB tables
Demiel: I see nothing here about innodb tables
Clyatt: Zamba: Exactly : All mysqlcheck does is to send CHECK TABLE and REPAIR TABLE to the server depending on options you used which by the way you didn’t tell us
Clyatt: Zamba: REPAIR doesn’t work for InnoDB