mysql

mysql view performance

I have a table for about 100,000 users in it. First Case: explain select state, count(*) as cnt from users where state = 'ca' when i do an explain plan for the above query i got the cost as 5200 Second Case: Create or replace view vw_users as select state, count(*) as cnt from users Explain select cnt from vw_users where state = '...

PHP returns invalid MySQL resource

$LDATE = '#' . $_REQUEST['LDateDay'] . '/' . $_REQUEST['LDateMonth'] . '/' . $_REQUEST['LDateYear'] . '#'; $RDATE = '#' . $_REQUEST['RDateDay'] . '/' . $_REQUEST['RDateMonth'] . '/' . $_REQUEST['RDateYear'] . '#'; include("../../sql.php"); $myconn2 = mysql_connect(/*removed*/, $username, $password); mysql_select_db(/*...

Help speed this MySQL query which is taking too long because of an aggregate function

Hi all, I am using a following query in MySQL for fetching data from a table. Its taking too long because the conditional check within the aggregate function.Please help how to make it faster SELECT testcharfield , SUM(IF (Type = 'pi',quantity, 0)) AS OB , SUM(IF (Type = 'pe',quantity, 0)) AS CB FROM Table1 WHERE sequence...

Advanced search functionality

I have a website with a jQuery based autocomplete search functionality which works great. Currently though I have just one search box for all categories, what I want is for someone to be able to type in, say for example, dorian gray dvd (in any order) which will search for dorian gray within the dvd category. What this will require then...

How do I change mysql settings so that it is default UTF-8 for everything?

I am getting "ASCII encoding" errors when I insert into my database because I did a fresh install of the MYSQL. I'd like to change the default to UTF-8 again. This is the error I'm getting because MYSQL is not set in UTF-8 mode: UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-1: ordinal not in range(128) ...

SELECT USING COUNT in mysql

Hi, I have a very large database with about 120 Million records in one table.I have clean up the data in this table first before I divide it into several tables(possibly normalizing it). The columns of this table is as follows: "id(Primary Key), userId, Url, Tag " . This is basically a subset of the dataset from delicious website. As I s...

Fetch HTML page and store it in MYSQL- How to

Hi, What's the best way to store a formatted html page with CSS on to MYSQL database? Is it possible? What the column type should be? How to retrieve the stored formatted HTML and display it correctly using PHP? What if the page I would like to fetch has pics and videos, show I store the page as blob What's the best way to fetch a pa...

Why am I getting this UnicodeEncodeError when I am inserting into the MySQL database?

UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 2: ordinal not in range(128) I changed my database default to be utf-8, and not "latin"....but this error still occurs. why? This is in my.cnf. Am I doing this wrong? I just want EVERYTHING TO BE UTF-8. init_connect='SET collation_connection = utf8_general_c...

How to select a limited amount of rows for each foreign key? (and how to specify the relatioship)

Hi, recently I have posted this question and now I want the same query but with one relationship. Now I have the "feeds" table and the "feed_entries" table. I want a limited amount of rows per category. The query above make use of one table with category_id field: SELECT x.* FROM (SELECT t.*, CASE WHEN @cat != t.category_id THEN @rown...

Merging MySQL row entries into a single row

I've got two tables, one for listings and another representing a list of tags for the listings table. In the listings table the tag ids are stored in a field called tags as 1-2-3-. This has worked out very well for me (regular expressions and joins to separate and display the data), but I now need to pull the titles of those tags into a...

MySQL: Use CASE/ELSE value as join parameter

I'm trying to join the NAME and PHOTO from USERS table to the TRANSACTIONS table based on who is the payer or payee. It keeps telling me can't find the table this -- What am I doing wrong? SELECT `name`,`photo`,`amount`,`comment`, ( CASE `payer_id` WHEN 72823 THEN `payee_id` ELSE `payer_id` END ) AS `this` FROM `transact...

Appending data to a MySQL database field that already has data in it.

I need to "add" data to a field that already contains data without erasing whats currently there. For example if the field contains HTML, I need to add additional HTML to the field. Is there a SQL call that will do this or do I need to call the data in that field, concatenate the new data to the existing data, and reload it into the d...

multple inner joins 3 or more crashes mysql server 5.1.30 opensolaris

when doing simple query on 4 inner joined tables, the server crashes with the output below appearing in the the mysql .err file. eg. select * from table1 inner join table2 on table1.a = table2.a and table1.b = table2.b inner join table3 on table2.a = table3.a and table2.c = table3.c inner join table4 on table3.a = table4.a and table3.d ...

Slow MySQL Query not using filesort

I have a query on my homepage that is getting slower and slower as my database table grows larger. tablename = tweets_cache rows = 572,327 this is the query I'm currently using that is slow, over 5 seconds. SELECT * FROM tweets_cache t WHERE t.province='' AND t.mp='0' ORDER BY t.published DESC LIMIT 50; If I take out either the WHER...

"No database selected" even when db clearly selected

One of my webpages gets a recurring error: "No database selected", even though the DB is selected. Right about now it's a 50-50 chance whether the page will load just fine, or whether I receive this error. After one or two reloads, the page works again. I am including the exact same connection file on my other pages, and I don't have...

Deleting a user > need to also delete their project, and then activities for that project? (PHP, MySQL)

Hi guys, Really stuck with this... basically my system has 4 tables; users, projects, user_projects and activities. The user table has a usertype field which defines whether or not they are admin or user (by an integer)... An admin can create a project, create an acitivity for the project and assign a user (limited access user) an acti...

utf8 and unicode getting warning messages in mysql

I have a mysql table. When I try to insert, I get this: Warning: Incorrect string value: '\xAE</...' for column 'value' at row 1 mysql> show create table Configurations; | Configurations | CREATE TABLE `Configurations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `ckey` varchar(255) NOT NULL, `value` ...

is there a mutex around 'checking permissions' and 'open tables' code in mysql 5.1/xtradb 6?

question comes up from some behavior i'm seeing. we're using mysql 5.1.36, xtradb release 6 on an 8 core box. when dropping a database with a lot of tables, mysql takes a long time to check permissions for each table before dropping them. i understand this to be normal behavior, at least with recent versions of mysql. however, when d...

Why is this MySQL Join Statement not Working?

Here is my code: $query2 = mysql_query("SELECT * FROM categories WHERE parent = $id JOIN SELECT * FROM posts WHERE main_nav_page = '$idTwo'"); while ($row2 = mysql_fetch_assoc($query2)) { $id = $row2['id']; $name = $row2['name']; $slug = $row2...

Update n number of records using JPA with Optimistic locking

I have a table with a column called "count" and I want to run a cron job that will trigger a statement that performs a SQL like this: update summaryTable set count=4; Note that there might be concurrent threads reading & changing the value for "count" when this cron job is triggered. The table has a version column to support Optimisti...