BETWEEN, IN, , .... not using index with floats

[Nacho Garcia]

Hi, im working with google maps and im and trying to do this, but i cant
make a good query of it.

I want to select elements between a given latitude and longitude from this
table:

*CREATE TABLE `images` (*

*`id_img` bigint(20) unsigned NOT NULL auto_increment,**
**`filename` char(50) NOT NULL,**
**`extension` enum('jpg','jpeg','gif','png') NOT NULL,**
**`lat` float(10,6) NOT NULL,**
**`lng` float(10,6) NOT NULL,*

*PRIMARY KEY (`id_img`),**
**KEY `lat` (`lat`,`lng`)
**) ENGINE=InnoDB  DEFAULT CHARSET=utf8*

**

**

**


im trying with this query and some similars but all of them scans all the
table, and i dont know why


SELECT *
FROM `images`
WHERE lat BETWEEN 29.993002 AND 49.410973
AND lng BETWEEN -40.209960 AND 32.871093


id   select_type   tabletypepossible_keys  key
key_len   ref   rows Extra
1SIMPLEimagesALL lat   NULL
 NULL   NULL  108  Using where


thanks in advance

JOIN / NOT JOIN differences

[Nacho Garcia]

hi, i have this table

*TABLE friends:
*id_usr  INT
id_friend   INT

and i have a query in which i return friends from a given user and data
related to each of them stored in some other tables.

So i do this:

SELECT F.id_friend, M.status, P.firstname, P.lastname, IF( UNIX_TIMESTAMP( )
- UNIX_TIMESTAMP( C.lastConnection )  240, C.status, 'disconnected' ) AS
'connectionstatus'
FROM friends F, user_connections C, user_personaldata P, user_statusmessages
M
WHERE F.id_usr = 1
AND C.id_usr = F.id_friend
AND P.id_usr = F.id_friend
AND M.id_usr = F.id_friend
ORDER BY connectionstatus ASC

but i have seen that if there is no rows matching a friend of the user in
the other tables, mysql ignore index and scan all the table.


this is not happening with joins.

SELECT F.id_friend, M.status, P.firstname, P.lastname, IF( UNIX_TIMESTAMP( )
- UNIX_TIMESTAMP( C.lastConnection )  240, C.status, 'disconnected' ) AS
'connectionstatus'
FROM friends F
LEFT JOIN user_connections C ON C.id_usr = F.id_friend
LEFT JOIN user_personaldata P ON P.id_usr = F.id_friend
LEFT JOIN user_statusmessages M ON M.id_usr = F.id_friend
WHERE F.id_usr = 1
ORDER BY connectionstatus ASC

why is the first query scanning all the table if a row is missing? , there
is any performance different between those queries ?

i've read that using index between larges tables is a bad choice, so i have
doubts

help with DB design / query please !

[Nacho Garcia]

hello,

im trying to make a DB for a message system.
the best way i have made is this:

*TABLE conversations* (informacion de cada conversacion)
.
i*d_conversation  (bigint)
count(smallint)  updated every time a new message is
added to this conversation
lastmessagetime   (timestamp) **updated every time a new message is added to
this conversation**
*

*INDEX (id_conversation,lastmessage)**
*
*
**TABLE user_conversations*  (relacion usuario-conversacion)
..
*id_user   (int)
id_conversation   (bigint)
**read   (bool)
**
**INDEX (id_conversation)
INDEX (id_user)*


*TABLE messages  * (mensajes on every conversation)
...
id_conversation   (bigint)
id_writer (int)
message(varchar)
time   (timestamp)

INDEX (id_conversation,time)


my problem is that i need to query those things and i dont know how to do
that.

*Conversation list of a given user  with `read`, number of messages
(`count`) , date of last message  (i store this value on `conversation`
table) and who did it
Order by time of the last message of each conversation*
i have tried everything i could but im not getting a good solution:

SELECT UC.id_conversation, UC.read, C.count, UNIX_TIMESTAMP( ) -
UNIX_TIMESTAMP( lastmessage ) , M.message, M.id_usr
FROM user_conversations UC, conversations C, messages M
WHERE UC.id_usr = 1
AND C.id_conversation = UC.id_conversation
AND M.id_conversation = UC.id_conversation
AND M.time = C.lastmessage
ORDER BY C.lastmessage DESC
LIMIT 0,10

thats giving me an ALL in `conversations` as result
id select_typetable type possible_keys key
   key_len ref rows
Extra
1 SIMPLE UCrefid_conversation,id_usr id_usr
 4  const 3
Using temporary; Using filesort
1 SIMPLE M  reforderbytime
orderbytime  8   netlivin3.UC.id_conversation1
1 SIMPLE C  ALL  id_conversation   NULL
 NULL NULL3 Using
where

im completely lost here, ill really appreciate any help.
thanks.

how to use index with order by here

[Nacho Garcia]

Hi, im having troubles with one query, hope someone can help.

on this table:

messages:
id_fromint(10)
id_toint(10)
textvarchar(1000)
time

with index on id_form and id_to

i want to get messages sent from one user to another and vice versa order by
time.
let say we want to search messages between user 1 and 2:
i'm doing:

SELECT *
FROM messages
WHERE id_from in (1,2)
AND id_to in (1,2)
ORDER BY time

but that gives me a filesort in all rows matching the where clause, and
thats not good. I tried by indexing id_from, id_to, time but thats not
working of course.

any help would be really appreciate.

help with query

[Nacho Garcia]

Hi, im having a lot of trouble with one query, i hope someone could give me
a hand with this, i would be really grateful

these are the tables:

TABLE friends
id_usr INT
id_friend INT
with INDEX on (id_usr,id_friend)

TABLE status
id_usr INT
lastConnection
 (other irrelevant info)
with INDEX on (id_usr,lastConnection)


im trying to get friends of a given id_usr ordered by lastConnection.

i tried a lot, the simplest query give me the best results

explain SELECT F.id_friend,S.lastConnection
FROM friends F, user_status S
WHERE F.id_friend = S.id_usr
AND F.id_usr = 1
ORDER BY lastConnection

id select_type table type possible_keys key key_len ref rows Extra   1
SIMPLE F ref id_usr id_usr 4 const 20 Using temporary; Using filesort  1
SIMPLE S ref id_usr id_usr 4 netlivin3.F.id_friend 3 Using index
it's really bad, but at least it's ref type so only read the rows of friends
table matching id_usr=1

**

unnormalize db here is more efficient?

[Nacho Garcia]

Hi, i hope this is the right place for this basic question.
i have a table like this:

TABLE elements
`id_element` INT UNSIGNED NOT NULL ,
`name`  VARCHAR(100),
`date` DATE
...

and other table with the comments of every element.

TABLE elements_comments
`id_element` INT UNSIGNED NOT NULL ,
`comment` TEXT

each element can have a lot of comments, so,
what's more efficient, unnormalize the database and keep track of the amount
of comments (using a trigger fired when a comment is wrote or deleted) such
as

TABLE elements
`id_element` INT UNSIGNED NOT NULL ,
`name`  VARCHAR(100),
`date` DATE,
`comments`  SMALLINT UNSIGNED
...

or count the comments on elements_comments table.
I guess the last option could be very slow with lot of rows because mysql
has to scan all of them .. and the first option seems to be very fast.

can someone help me with that?

thanks a lot

triggers sintax error with IF

[Nacho Garcia]

Hi, im struggling with triggers but i can make this works because of bad
sintax, i hope someone can give me a hand

CREATE TRIGGER trigger_actualize AFTER INSERT ON usuarios_imgvid
   FOR EACH ROW
IF NEW.type=IMG
THEN
UPDATE users_status E SET images=images+1 WHERE E.id_usr = NEW.id_usr
END IF;


this is giving me an error of sintax

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ''
at line 5


thanks a lot