4 replies to this topic

[lol33d]

hi guys

i'll using mysql

please select an better query for those tables:

CREATE TABLE IF NOT EXISTS `msgUSR` (

  `user_id` int(11) NOT NULL AUTO_INCREMENT,

  `nick` varchar(255) NOT NULL,

  `email` varchar(255) NOT NULL,

  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`user_id`)

);


CREATE TABLE IF NOT EXISTS `msgING` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `msg` text NOT NULL,

  `user` int(11) NOT NULL,

  `to_user` int(10) unsigned NOT NULL,

  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

);

Query1:

SELECT `msgING`.`msg` , `msgUSR`.`nick` , `msgUSR`.`email`

FROM `msgUSR`

RIGHT JOIN `msgING` ON `msgUSR`.`user_id` = `msgING`.`user`

WHERE `msgUSR`.`user_id` = '3'

[Showing rows 0 - 21 (22 total, Query took 0.0004 sec)]

Query2:

SELECT `msgING`.`msg` , `msgUSR`.`nick` , `msgUSR`.`email`

FROM `msgUSR`

RIGHT OUTER JOIN `msgING` ON `msgUSR`.`user_id` = `msgING`.`user`

WHERE `msgUSR`.`user_id` = '3'

[Showing rows 0 - 21 (22 total, Query took 0.0004 sec)]

Query3:

SELECT `msgING`.`msg` , `msgUSR`.`nick` , `msgUSR`.`email`

FROM `msgUSR`

LEFT JOIN `msgING` ON `msgUSR`.`user_id` = `msgING`.`user`

WHERE `msgUSR`.`user_id` = '3'

[Showing rows 0 - 21 (22 total, Query took 0.0005 sec)]

Query4:

SELECT `msgING`.`msg` , `msgUSR`.`nick` , `msgUSR`.`email`

FROM `msgUSR`

LEFT OUTER JOIN `msgING` ON `msgUSR`.`user_id` = `msgING`.`user`

WHERE `msgUSR`.`user_id` = '3'

[Showing rows 0 - 21 (22 total, Query took 0.0006 sec)]

Query5:

SELECT `msgING`.`msg` , `msgUSR`.`nick` , `msgUSR`.`email`

FROM `msgUSR` , `msgING`

WHERE `msgUSR`.`user_id` = `msgING`.`user`

AND `msgUSR`.`user_id` = '3'

[Showing rows 0 - 21 (22 total, Query took 0.0004 sec)]



thank you so much

[WingedPanther]

It depends on what you want returned. 1 and 2 are the same query. 3 and 4 are the same query. 5 is equivalent to an inner join.

There is no "better", just correct for what you need returned.

[lol33d]

thank you

all queries its ok?

in some queries time is 0.0004 sec or 0.0005 sec, and some is 0.0006 sec

0.0004 sec is good?

[WingedPanther]

Here's the important thing to understand: they return different results! Using a query that has shorter execution time but gives you the wrong results doesn't help you. Also, the execution time depends on the processor speed, indexes number of records in each table, number of records that match, etc, etc, etc, etc, etc.

[Vaielab]

Plus, there is no real difference between 0.0004 and 0.0006
Maybe while you were doing the second query, your os was doing something, thus it take more time, and had nothing to do with the real query time.