… outcome: mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’; On a table like this: CREATE TABLE `cast_info…) NOT NULL, `person_role_id` int(11) DEFAULT NULL, `note` varchar(250), `nr_order` int(11) DEFAULT NULL, `role_id` int… technique, did actually take more time to execute: mysql> SELECT role_id FROM cast_info WHERE role_id = 1 and note like…
Comment: GROUP_CONCAT useful GROUP BY extension
April 29, 2009 By 49 Comments
…` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `nom` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM … ‘aurelien’); mysql> SELECT @lid := CAST(GROUP_CONCAT(id) AS CHAR) FROM `user` WHERE `nom` in(‘renaud’, ‘julie’, ‘lucy’); +—————————————-+ | @lid := CAST(…
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
… tb1.vid = CONCAT(‘prefix-’, tb2.id) with tb1.vid – indexed varchar(100) and tb2.id – int(11) column. No matter what…, you can use an explicit type cast… OK, let’s check if that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT(‘prefix-’, CAST(tb2…
