I've been reading through some of guides on database optimization and best practices and a lot of them suggest not using boolean flags at all in the DB schema (ex http://forge.mysql.com/wiki/Top10SQLPerformanceTips). However, they never provide any reason as to why this is bad. Is it a peformance issue? is it hard to index or query prope...
Say I have three properly normalised tables. One of people, one of qualifications and one mapping people to qualifications: People: id | Name ---------- 1 | Alice 2 | Bob Degrees: id | Name --------- 1 | PhD 2 | MA People-to-degrees: person_id | degree_id --------------------- 1 | 2 # Alice has an MA 2 ...
Is there a way to specify the database engine to be used for fixtures in CakePHP test fixtures? Some of my models depend on database transactions, and I would like to write some tests for their correct behavior. Currently I'm simply auto-importing the schema, but the tables get created with MySQL's standard MyISAM engine, which doesn't s...
This is my current query - its not getting the required result. I want it do display all of the "resources" even if they dont have a connection. SELECT * FROM (`user_permissions`) JOIN `user_groups` ON `user_groups`.`id` = `user_permissions`.`role` JOIN `user_resources` ON `user_resources`.`id` = `user_permissions`.`resource` WHERE `rol...
Would this be better as a stored procedure or leave it as is? INSERT INTO `user_permissions` ( `user_id`, `object_id`, `type`, `view`, `add`, `edit`, `delete`, `admin`, `updated_by_user_id` ) SELECT `user_id`, $object_id, '$type', 1, 1, 1, 1, 1, $user_id FROM `user_permissions` WHERE `object_id` = $object_id_2 AND `t...
this is the query SELECT members.memberID, members.salutation, members.firstName, members.middleName, members.lastName, members.suffix, members.company, ...
At the moment I can get the results I need with two seperate SELECT statements SELECT COUNT(rl.refBiblioID) FROM biblioList bl LEFT JOIN refList rl ON bl.biblioID = rl.biblioID GROUP BY bl.biblioID SELECT GROUP_CONCAT( CONCAT_WS( ':', al.lastName, al.firstName ) ORDER BY al.authorID ) FROM biblioList bl LEFT JOIN biblio_author b...
Say that I have a long, expensive query, packed with conditions, searching a large number of rows. I also have one particular condition, like a company id, that will limit the number of rows that need to be searched considerably, narrowing it down to dozens from hundreds of thousands. Does it make any difference to MySQL performance whe...
Hi in my db there are 5 fields (id, list_date, amount, total, m_from) and contain data, for example : 1 - 1/1/2010 - 10 - 50 - 'example111' 1 - 1/1/2010 - 10 - 50 - 'example111' 1 - 1/4/2010 - 10 - 50 - 'example154 1 - 1/1/2010 - 10 - 50 - 'example111' 1 - 1/5/2010 - 10 - 50 - 'test' I need to know how I can g...
Hello, How do I create CacheDependency with mysql? ...
He guys, I'm stuck with a problem and I hope someone can help me out. I have a date. For example 2009-10-1. This date is used to check in which season I am working. This could be summer or winter. If whe are in the summer the table to use for my inner join whould be 'summer09_rooms'. If winter 'winter09_rooms'. So I basicly whant to do ...
I recently encountered a problem in my production database due to MySQL bug. The bug is in the TimeStamp column. To avoid any inconvenience, I want to migrate the database to either Sql Server. Is there any FREE tool to easily and reliably migrate data and table structures? ...
I have a candidate table say candidates having only id field and i left joined profiles table to it. Table profiles has 2 fields namely, candidate_id & name. e.g. Table candidates: id ---- 1 2 and Table profiles: candidate_id name ---------------------------- 1 Foobar 1 Foobar2 2 ...
I'm going to optimize a MySQL embedded query with a view, but I'm not sure whether it will give an effect: SELECT id FROM (SELECT * FROM t); I want to convert it to: CREATE VIEW v AS SELECT * FROM t; SELECT id FROM v; I've heard about "indexed views" in SQL Server, but I'm not sure about MySQL. Any help would be appreciated. Thank...
Hi guys, What can I do to ensure that replication will use latin1 instead of utf-8? I'm migrating between an MySQL 5.1.22 server (master) on a Linux system and a MySQL 5.1.42 server (slave) on a FreeBSD system. My replication works well, but when non-ascii characters are in my varchars, they turn "weird". The Linux/MySQL-5.1.22 shows ...
I looked for a way to scan database for a specific table. For example i have: Database: system_ultimate Table: system_settings And let us say, that one doesn't know precise name of the table. He only knows, that it is some how connected to the word settings. How could he search for that table name then? I understand, that usually pe...
Currently, my checkboxes are built by a static array: $choices = array( 'key_1' => 'Name 1', 'key_2' => 'Name 2', ... 'key_n' => 'Name n'); <? foreach (@choices as $key => $choice) { echo "<input type="checkbox" name='keys[]' value='$key'/> $choice <br />"; } ?> and I access the return values by: $_POST['keys']...
$user = &JFactory::getUser(); $uid = $user->get('id'); //works $umail = $user->get('email'); //works $unamed = $user->get('posname'); //does not work Why can't I use JFactory to get the fields that I have added by myself on the Joomla user table. Is there another simple way they could be gotten? ...
Hi Guys, I'm looking to construct a script that would go through an XML file. Would find specific tags in it, put them in a table and fill the table with specific tags within them. I'm using MySQL 5.1 so loadXML isn't an option and I think that ExtractData() method wont be much use either.. but I don't really know. What would be the bes...
Hi to all, I need to integrate available payment gateways will be: Webmoney, Liberty reserve, Perfect money in my website using php scripting. if you have all ready done please provide code to us or give suggestions how to integrate. Thanks & Regards Akil ...