Hello everyone, I tried to optimize a MySQL query which sort multiple varchar columns: SELECT * FROM tickets LEFT OUTER JOIN customers ON customers.id = tickets.customer_id LEFT OUTER JOIN locations ON locations.id = tickets.location_id ORDER BY customers.name, locations.name; The ORDER BY statement seems to cost lot of time(~100ms) ...
I have a table T that has a column A. A started with a default of NULL. 40 rows later, I changed the default to 1. Three rows had a value of 2. I tried to select all the rows where column A where not 2 and set them to the new default of 1 (which hadn't happened automatically when I altered the table). I first tried: update T set A=...
This is a college project: I have a database ( mysql or postgresql doesn't matter ) with 100 billion of posts and I need to search ( as fast as possible ) a generic keyword. Every post is 500-1000 keywords. This isn't only a database issue but also a software ( for indexing or other ) issue. How can I do that ? I could use some adva...
Hi, I have developed a download/upload manager script that will be used by many people. When i upload a file via POST method it is stored in a folder called files , the files folder is within another folder called download-manager. Now it seems when i upload via the POST method 0666 CHMOD works when i want to rename, delete the file b...
I have a table like this... select * from myescape; +-----------+ | name | +-----------+ | shantanu' | | kumar's | +-----------+ 2 rows in set (0.00 sec) I need to replace the single quote ' with \' I will also need to escape double quotes and backslash. ...
Hi guys. Just after some opinions on the best way to achieve the following outcome: I would like to store in my MySQL database products which can be voted on by users (each vote is worth +1). I also want to be able to see how many times in total a user has voted. To my simple mind, the following table structure would be ideal: tab...
Hi There, I've been battling with some SQL and can't seem to get my head around it. I have two tables, one with the list of categories and another with all my articles. What i'm trying to do is find how many articles are present for each category. Here is the SQL I have so far SELECT DISTINCT COUNT( po.post_Cat_ID ) AS Occurances, c...
The idea is to run a sort of ranking MySQL command such as: $sql = mysql_query("SELECT f_score FROM ".TBL_FACTIONS." ASC"); EDIT: I do need to add a WHERE f_id = $id in here too. The numeric value in my MySQL database table in the *f_score* column should determine the rank. f_score contains only numeric values, from 0 to anythin...
Hi, I found a similar post about this but still not sure. As i am making my guestbook and so forth multilanguage i changed the collation to uft8_unicode_ci in mysql, everything works as it should, something that i did not think of was the type i use, my guestbook is multilanguage and for the name field a user cannot enter more than 50 ...
Hi, Background: We have a class which is used for accessing the mysql database. The methods within this class form a new mysql connection if a link isnt supplied as a parameter. The way we've used the class in the system (not the database class) is to declare a database object and call the appropriate method and let the object scope ...
Hi, If I were designing a new system, should I have each function open and close the mysql connection as and when needed OR should I form one connection and keep that as a "globally accessible variable" for that browser session? Why is it a bad idea, if indeed it is? ...
Hi have a 2 tables organized in this way: table_users |user_id|username| table_products |product_id|user_id| Now, I have some orphan entries in 'table_products'. With this query I check how many products I have: SELECT count(product_id) FROM table_products This query show me the number of the products associated to the users: SE...
I create 3 tables by Hibernate, how to return a List<task> when set task_status = ? and set create_user_id = ?, task_status in "dotask" table, create_user_id in task table. How to write the "HQL"? Here is mine, but I can't make it true with it: String hql="from task t left join dotask d on d.task_status = ? and t.create_user_id = ? li...
Hello, I have such a problem. I'm using EF1 with VS2008 SP1 & MySQL with MySQL Connector/Net 6.3.4 My database schema looks like this: CREATE TABLE IF NOT EXISTS `credential` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; CREATE T...
How would you send long text from jquery to mysql becuase i have been trying use the .load function and for some reason i cannot get the text through. Var new_text = "oehieifewfewiofeio"; $("#test").load("actions.php?act=save&new_text="+new_text); Thank you, ...
If I write this query, EXPLAIN SELECT * FROM `smth` WHERE d_id = 9 it checks one row instantly. But when I use IN, for example EXPLAIN SELECT * FROM `smth` WHERE d_id IN (9, 3) it checks all rows. What should I do instead of IN if index doesn't help there? ...
Hello guys! Im moving my site from webfaction to linode VPS. My VPS have 512MB of ram with Fedora Core 13, and when i start apache/mysql and access the site from browser, my site take MINUTES to load, and memory looks like this Mem: 510652k total, 504652k used, 6000k free, 3460k buffers Swap: 1048568k total, 5260k ...
First of all I'm an Ubuntu newbie so bare with me. I have a MySQL database I'm able to connect via the command prompt and the MySQL administration tool but using the same credentials in a Web application in a Tomcat server on the same server gives me this error: javax.servlet.ServletException: org.apache.commons.dbcp.SQLNestedExcepti...
I have this query below. There are 4 main tables involved: tblOrder, tblItems, tblOrder_archive, tblItem_archive. Orders and Items get moved over to the archived versions of the tables after a few months as not to slow down the main table queries. (sales and traffic is REALLY HIGH). So to get sales figures, i select what i need from each...
Hi there. Got a fairly straightforward system: Table of articles, table of tags, table of article_tags containing the IDs of the tag and the article it's assigned to. When I want to grab a list of all of my articles and all of their tags, I have to do some GROUP_CONCAT() querying to get a comma-separated list of the tags, which I can ...