Trying to optimize slow MySQL query


You should have a look at "Using temporary; Using filesort". This is what is slowing your SQL down as it is writing to disk and sorting on disk. You might want to have a look at increasing the sort_buffer_size on your server to have the sorts performed in memory and not on disk.
Posted On: Monday 31st of December 2012 01:05:01 AM Total Views:  434
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Very slow joins table (EXPLAIN included)

I'm very confused about my dirty quires. So, I don't know what's wrong with my JOINS why they are so slow I have 2 table with large data: forum_messages :: 267,808+ row totalforum_users :: 332,042+ total Here is my Query: PHP Code: SELECTusers.type_id,\t\t\t\t\t\t\t\tusers.full_name,\t\t\t\t\t\t\t\tusers.user_id,\t\t\t\t\t\t\t\tmsgs.message_id,\t\t\t\t\t\t\t\tmsgs.message_body,\t\t\t\t\t\t\t\tmsgs.msg_is_read,\t\t\t\t\t\t\t\tmsgs.user_send_id,\t\t\t\t\t\t\t\tUNIX_TIMESTAMP(msgs.sent_date)ASsnt_date\t\t\t\t\t\tFROM\t\t\t\t\t\t\t\t`forum_messages`ASmsgs\tINNERJOIN`forum_users`ASusersONmsgs.user_send_id=users.user_id\t\t\t\t\t\tWHERE\t\t\t\t\t\t\t\tmsgs.user_receive_id=1\t\t\t\t\t\t\t\tANDmsgs.msg_del_receive=1\t\t\t\t\t\t\t\tANDmsgs.is_shows=1\t\t\t\t\t\tORDERBYmsgs.sent_dateDESCLIMIT10 forum_messages Indexes: 1.jpg forum_messages EXPLAIN: 2.jpg forum_users Indexes: 3.jpg Is there any idea please
VIEWS ON THIS POST

173

Posted on:

Thursday 25th October 2012
View Replies!

trying for multiplication in query

I think someone may tell me to do this post-process but here goes. querying a table for several records and two cols from each - quantity and price. eg Code: | id | quantity | price | | 2 | 34 | 11.68 | | 3 | 3 | 5.48 | I would like the returned result to the multiplication of quantity and price, totalled over all records. ie 34 * 11.68 + 3 * 5.48 ___________ result returned 413.56 do-able\t bazz
VIEWS ON THIS POST

134

Posted on:

Thursday 25th October 2012
View Replies!

MySQL is real slow

We are currently running a WordPress blog in a Windows environment, due to other websites that we run on the same server. And for a long period of time things were going fine. But in the last few days the homepage in particular has become very slow. I can't blame the server as the other sites run well, the server stats are no where near maxing out, but it just seems that every so often the homepage seems to take a very long time to load. The homepage itself is real busy, and we put code at the bottom of the site to tell us the query count and time taken. Now we regular see the home page taking only a couple of seconds to run all the queries, but more and more often now it takes upwards of 20-30 and I have even seen 40 seconds time to run the queries to build the homepage. Could this be a MySQL issue, a PHP issue, a WordPress issue I know it's not a plugin issue as I copied the database, had a fresh install of WordPress and found similar issues with speed.
VIEWS ON THIS POST

95

Posted on:

Thursday 25th October 2012
View Replies!

MySql responding slow

I have a difficult question now: in fact one that anybody who helps on a forum will hate because the information I can offer is minimal and not complete. I have a LAMP setup (Fedora 12, Apache 2, MySql 5 and PHP 5) which is exhibiting a very strange behaviour: MySql response times are very slow. Let me explain this better. The website which is built on this server (and which uses a number of MySql queries at each page load) responds fine - or at least with no perceptible slow down. The repsonse time of MySql gets slow only when I run queries through PhpMyAdmin or through some small PHP scripts. Here are some further notes: 1. By slow response time I mean that although the query is still run in 0.0002s (as reported by PMA) it takes at least 10 seconds before the query is executed. 2. I have tried emptying the DB and leaving only 10 records in one table, but the problem persists. 3. "SELECT * FROM" queries seem to behave normally. 4. Examples of queries that I know have a problem are: SELECT * FROM table WHERE id = '1' ALTER TABLE INSERT I am starting to think this might be a setup issue, but I have no clue where to look. I also understand this is very limited information, so I will appreciate anybody who will take the time to point me in the right direction. I have tried activating the cache, turning on slow query loggin (no slow queries) and have tried "skip-name-resolve", but none of these things have worked. Here below is my my.cnf file. Code: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Recommendations from MySqlTuner max_connections = 150 max_user_connections = 150 key_buffer = 36M myisam_sort_buffer_size = 64M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M open_files_limit = 2048 table_cache = 1024 wait_timeout = 60 interactive_timeout = 30 query_cache_size = 512M query_cache_limit = 512M thread_cache_size = 4 table_cache = 256 log-slow-queries = /var/log/mysql-slow.log long_query_time = 1 max_allowed_packet = 1M max_connect_errors = 10 max_heap_table_size = 64M tmp_table_size = 64M # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines and adjust the connectstring as needed. #ndbcluster #ndb-connectstring="nodeid=4;host=localhost:1186" [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [ndbd] # If you are running a MySQL Cluster storage daemon (ndbd) on this machine, # adjust its connection to the management daemon here. # Note: ndbd init script requires this to include nodeid! connect-string="nodeid=2;host=localhost:1186" [ndb_mgm] # connection string for MySQL Cluster management tool connect-string="host=localhost:1186" Thank you very much for your help, Adrien
VIEWS ON THIS POST

189

Posted on:

Thursday 25th October 2012
View Replies!

mysql LEFT JOIN very slow

my query takes almost 2 minutes to query which is very slow. My 'clicks' and 'links' tables both are constantly updated with new data so this could be a reason but I've also been reading about doing indexes but not familiar with this at all. What can I do to get the fastest query out of my sql. Code: SELECT l.title,l.url,l.stime,l.linkid FROM links l LEFT JOIN clicks c ON (c.linkid2 = l.linkid) ORDER BY l.linkid DESC
VIEWS ON THIS POST

262

Posted on:

Thursday 25th October 2012
View Replies!

Error trying to import to mysql...help please

I exported a small database to my computer Desktop. A .sql file. I asked my new web host to help me import it my new web mysql. I uploaded it and they attempted it directly to mysql(no phpmyadmin) and they replied "the importing of the backuped file to your database gives errors, and overwrites existing settings" and then provided the foloowing information below. Can someone help me tell me what this means and explain what went wrong and how this can be remedied Thank you. Query: CREATE TABLE `accounts` ( `account_id` bigint(20) NOT NULL auto_increment, `user_name` varchar(50) NOT NULL, `first_name` varchar(100) NOT NULL default '', `middle_initials` varchar(10) NOT NULL default '', `last_name` varchar(100) NOT NULL default '', `password` varchar(100) NOT NULL, `security_question` varchar(250) NOT NULL default '', `security_question_answer` varchar(250) NOT NULL default '', `email` varchar(200) NOT NULL, `phone` varchar(100) NOT NULL default '', `mobile_phone_number` varchar(100) NOT NULL default '', `account_type_id` int(11) NOT NULL default '1', `address` varchar(255) NOT NULL default '', `address_second` varchar(255) NOT NULL default '', `state` varchar(150) NOT NULL default '', `city` varchar(150) NOT NULL default '', `zip_code` varchar(150) NOT NULL default '', `country_id` int(11) NOT NULL default '0', `country_name` varchar(100) NOT NULL default '', `status_id` int(11) NOT NULL default '1', `status_title` varchar(50) NOT NULL, `date_creation` date NOT NULL, `date_last_login` date NOT NULL, `user_agreement` varchar(20) NOT NULL, `last_ip_address` varchar(20) NOT NULL, `activation_code` varchar(100) NOT NULL default '', `date_last_payment` date default NULL, `terms` varchar(3) default 'yes', `login_count` int(11) NOT NULL default '0', PRIMARY KEY (`account_id`), UNIQUE KEY `ix_accounts_email` (`email`), UNIQUE KEY `ix_accounts_user_name` (`user_name`), KEY `ix_accounts_first_name` (`first_name`), KEY `ix_accounts_family_name` (`last_name`), KEY `ix_accounts_country_id` (`country_id`), KEY `ix_accounts_status_id` (`status_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 Error occured at:2007-10-01 19:26:42 Line no.:59 Error Code: 1050 - Table 'accounts' already exists
VIEWS ON THIS POST

434

Posted on:

Thursday 25th October 2012
View Replies!

MySQL timing out? Really slow queries, already indexed.

For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got: Uptime: 220372 Threads: 39 Questions: 18748899 Slow queries: 808 Opens: 28723 Flush tables: 1 Open tables: 4096 Queries per second avg: 85.078 The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling. Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096: query_cache_min_res_unit = 4096 table_cache = 4096 transaction_prealloc_size = 4096 Do any of those have anything to do with this Am I even close Are any of those values from mysql_stat a big deal
VIEWS ON THIS POST

102

Posted on:

Thursday 25th October 2012
View Replies!

Why does the slow query log show more rows than exist?

Purely a question of curiosity: Why does my slow query log sometimes show more rows examined than the table being hit contains Code: # Time: 070528 17:14:57 # User@Host: counter[counter] @ localhost [] # Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647 SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7; Code: mysql> select count(*) from _1_log; +----------+ | count(*) | +----------+ | 111824 | +----------+ 1 row in set (0.00 sec)
VIEWS ON THIS POST

188

Posted on:

Thursday 25th October 2012
View Replies!

only built small DB's ... this is 200+ mill and it's slow

OK, this might be out of my league ... i've only built small db's for clients ... like a couple programs that fetch rates for various countries and similar things. i can get what i need done but this might need some more proficient skills. I have a new project, and getting the DB filled the 5% of where we want it to be is yielding troubling results. at about 2 million records, my queries are SSLLOOWW. In brief, I'm just doing a straight query against this table, I haven't gotten other tables involved. before the schema, here's what i'm trying to do. several companies will be uploading their inventories into my DB. we'd like to have 1000 customers, so if they each upload a few csv files totaling 150,000 lines ... i'll have 150,000,000 pieces of inventory. they are most likely uploading the same part #'s (which is part of my business design). so you might search for a 'MVP143' and pull up 50 companies that sell them. so that said, i need to be able to take a query, show the part #'s and list the companies (then users can click each company for more details). my parts table looks like this ... TABLE `parts` ( `partID` int(10) unsigned zerofill NOT NULL auto_increment, `clientID` smallint(4) unsigned zerofill NOT NULL default '0000', `type` char(2) NOT NULL default 'EX', `PartNumber` varchar(70) NOT NULL default '', `invDate` date default NULL, `Description` varchar(100) default NULL, `Manufacturer` varchar(25) default NULL, `ManDate` varchar(10) default NULL, `Quantity` varchar(7) default NULL, PRIMARY KEY (`partID`), KEY `PartNumber` (`PartNumber`) ) if i do a 5 character search against partNumber ... it's slow ... when i get 10 people a second running a part search when i have 7 million rows ... it will probably not even work. another thing ... people will be dumping their old data with new data liek every couple days. how will auto_increment function with this before they up new inventory, they will dump their old data. so, if you were me ... what would you be doing
VIEWS ON THIS POST

237

Posted on:

Thursday 25th October 2012
View Replies!

Help me optimize a complex query

Hi There, Thank you for taking a look at my post. I hope you can help me as I am new to SQL optimization. Here is my query used to get a list of properties from a real estate web site: Code: SELECT p.*, p.reference, p.agent_id, p.listing_type_id, p.purchase_price, p.num_bedrooms, p.num_bathrooms, p.pool, p.property_type_id, p.completion_date, p.holiday_base_rent, p.holiday_sleeps, p.monthly_rental_price, p.rental_start_date, a.type AS agent_type, t.type_name AS property_type_constant, a.company_name, a.name, c.constant_name AS country_name_constant, i.image_file_name, i.image_ext, i.image_title, area.location_name AS area_name, town.location_name AS town_name, area.zoom_level AS area_zoom, town.zoom_level AS town_zoom, adj1.adjective AS adjective1, adj2.adjective AS adjective2, rc.contract_name, ltn.in_sentence_name AS listing_type_constant, sp.id AS saved_property_id FROM jos_mh_properties AS p LEFT JOIN jos_mh_user_info AS a ON p.agent_id = a.user_id LEFT JOIN jos_countries AS c ON p.country_id = c.country_id LEFT JOIN jos_mh_property_types AS t ON p.property_type_id = t.id LEFT OUTER JOIN jos_mh_images AS i ON i.property_id = p.id AND i.image_name_id = ( SELECT MIN(image_name_id) FROM jos_mh_images WHERE property_id = p.id ) LEFT JOIN jos_mh_locations AS area ON p.area_id = area.link_id LEFT JOIN jos_mh_locations AS town ON p.town_id = town.link_id LEFT JOIN jos_mh_adjectives AS adj1 ON p.title_adj_id1 = adj1.id LEFT JOIN jos_mh_adjectives AS adj2 ON p.title_adj_id2 = adj2.id LEFT JOIN jos_mh_lt_rental_contracts AS rc ON p.contract_duration_id = rc.id LEFT JOIN jos_mh_listing_type_names AS ltn ON p.listing_type_id = ltn.id LEFT JOIN jos_mh_saved_properties AS sp ON p.id = sp.prop_id AND sp.user_id = 148 WHERE p.published = 1 AND p.listing_type_id = 1 GROUP BY p.id ORDER BY p.purchase_price ASC, p.holiday_base_rent ASC, p.monthly_rental_price ASC, p.num_bedrooms DESC, p.holiday_sleeps DESC LIMIT 0, 10 The query took more than 7 seconds to run the first time I ran it but now takes as little as 0.05 seconds (cached). Anyway... when I run from the site it takes way too long. I have read the big bad post about optimization but parts of it I simply do not understand. I think it might have something to do with needing to create indexes and I think the culprit is the table jos_mh_locations and that the 'link_id' column isn't an index and that the table contains almost 50000 records. Does this sound right If so... can I simply (and safely) add an index to the column Are there any other optimizations I can do I have tried to copy the EXPLAIN statement below - I am not sure it will format right though. Code: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p ALL NULL NULL NULL NULL 76 Using where; Using temporary; Using filesort 1 PRIMARY a ALL NULL NULL NULL NULL 81 1 PRIMARY c eq_ref PRIMARY PRIMARY 4 medhead_joom1.p.country_id 1 1 PRIMARY t eq_ref PRIMARY PRIMARY 4 medhead_joom1.p.property_type_id 1 1 PRIMARY i ALL NULL NULL NULL NULL 93 1 PRIMARY area ALL NULL NULL NULL NULL 45549 1 PRIMARY town ALL NULL NULL NULL NULL 45549 1 PRIMARY adj1 eq_ref PRIMARY PRIMARY 4 medhead_joom1.p.title_adj_id1 1 1 PRIMARY adj2 eq_ref PRIMARY PRIMARY 4 medhead_joom1.p.title_adj_id2 1 1 PRIMARY rc eq_ref PRIMARY PRIMARY 4 medhead_joom1.p.contract_duration_id 1 1 PRIMARY ltn eq_ref PRIMARY PRIMARY 1 medhead_joom1.p.listing_type_id 1 1 PRIMARY sp ALL NULL NULL NULL NULL 9 2 DEPENDENT SUBQUERY jos_mh_images ALL NULL NULL NULL NULL 93 Using where
VIEWS ON THIS POST

198

Posted on:

Thursday 25th October 2012
View Replies!

Is someone trying to hack the form?

When someone sign up, the system sends me an email. I should get following emails: Fistname Lastname just submitted online form. But I sometimes get following: x\\\';\\ aeea09b3-d4a5-44ec-abab-fa40fd90f33d just submitted online form. What do you think these scrambled characters Is someone trying to hack the system
VIEWS ON THIS POST

139

Posted on:

Thursday 25th October 2012
View Replies!

Help me optimize this query please

I just need it to return 1 record (the "next" client to call) but it doesn't return the correct one if I use TOP x. I'm worried about the joins with SELECTs inside, I think that might be slowing it down. It's running on MSSQL Server 2000. Clients has about 150k records, Calls has about 450k. Code: SELECT C.Id AS ClientId FROM Clients C INNER JOIN ClientDatasets CD ON CD.ClientId=C.Id INNER JOIN CampaignDatasets CaD ON CaD.DatasetId=CD.DatasetId AND CaD.CampaignId=16 LEFT OUTER JOIN ( SELECT Id, ClientId, COUNT(Id) AS CallCount FROM Calls WHERE CampaignId=16 AND StatusId IN(6,7,8) GROUP BY Id, ClientId ) DT ON DT.ClientId=C.Id LEFT OUTER JOIN ( SELECT Id, ClientId, COUNT(Id) AS NoCallCount FROM Calls WHERE CampaignId=16 AND StatusId IN(2,3) GROUP BY Id, ClientId ) NoDT ON NoDT.ClientId=C.Id WHERE C.StatusId NOT IN(2,3,4,5) AND (DT.CallCount < 3 OR DT.CallCount IS NULL) AND (NoDT.NoCallCount=0 OR NoDT.NoCallCount IS NULL) ORDER BY DT.CallCount ASC
VIEWS ON THIS POST

165

Posted on:

Thursday 25th October 2012
View Replies!

how to make a switch mysql to ms sql

, My current mysql database has grown in size and usage, and its giving so many problems that i'm thinking of switching from mysql to ms sql 2005. My current application is running on php. Is it good idea to switch I havnt found any post related to php/ms sql 2005. Give your opinons. And a tutorial on switching db's would really help.
VIEWS ON THIS POST

247

Posted on:

Thursday 25th October 2012
View Replies!

desktop mysql editor

Does anyone know if there is a desktop editor that I can use to delete colomns and edit other properties from a mysql database I downloaded to my desktop. I don't want to mess with the database on my server.
VIEWS ON THIS POST

279

Posted on:

Thursday 25th October 2012
View Replies!

Program to optimise mysql queries

Basically im looking for a program that will take input (my query) and produce an output with a much nicer, more efficient query. I have seen sql programs that do this sort of thing, but nothing for mysql. Just wonder if there is any programs that i have missed Tryed Toad for mysql, but that wasnt what i wanted, i dont want a program to admin a mysql database, simply to produce a more efficient query. SURELY there is a program that does such a task
VIEWS ON THIS POST

215

Posted on:

Thursday 25th October 2012
View Replies!

Showing the un-input-ed

i have two tables, phase and phasename in phasename,i have id & p_name in phase, i have id, pname_id, and cno_id let say phasename has 5 rows,id 1 to 5. how to use select to show the id of phasename which is not in phase yet. i.e:in table phase, cno_id=1 ,there is already rows consists of pname_id 1,2 and 3. how to make a select statement only to show the pname id 4 & 5 from phasename
VIEWS ON THIS POST

143

Posted on:

Monday 29th October 2012
View Replies!

Linked server

is there a way to create linked server between 2 mysql servers like in SQL SERVER
VIEWS ON THIS POST

409

Posted on:

Monday 29th October 2012
View Replies!

LIKE not liking me

$query = "SELECT * FROM ingest WHERE id_file IN (SELECT DISTINCT id_file FROM ingest WHERE '$trimmed' IN( subj1,subj2,subj3 ))"; I would like to Distinct id_file from ingest where LIKE '%$trimmed%' in etc....
VIEWS ON THIS POST

42

Posted on:

Monday 29th October 2012
View Replies!

Any one gone through this .?

, I need to add a custom paramenter to mysql i.e. --max_connect_errors=999999999. The problem is , MySql starts automatically ( /etc/init.d/mysql) . , so not able to add the parameter. I tried to stop mysql through rc.local & tried to run PHP Code: /usr/sbin/mysqld--basedir=/usr\t--datadir=/var/lib/my--max_connect_errors=999999999 didnt helped me. How to add paramenter in init. script , please correct my assumptions. I assume that this shell script reads the parameters from my.cnf file. if so, i would add the parameter in the file.
VIEWS ON THIS POST

160

Posted on:

Monday 29th October 2012
View Replies!

Find out how many visitors for a website

Hi; i'd like to ask how to count how many visitor come to my website For example; i got a table called visitors_table, it got some attributes like visitor_ip, visitor_date,id... and in the visitor_date 2008-11-20, there are some visitors come to my page 2008-11-20 10:00:00 58.96.75.130 2008-11-20 10:00:30 58.96.75.130 2008-11-20 10:01:30 61.135.168.39 2008-11-20 10:21:30 61.135.168.39 2008-11-20 10:21:31 67.195.37.186 then i said in the date of 20, there are 3 visitors come to my website and in the 2008-11-21,there are some visitors come to my page 2008-11-21 10:00:00 58.96.75.130 2008-11-21 10:00:30 58.96.75.130 2008-11-21 10:01:30 61.135.168.39 2008-11-21 10:21:30 61.135.168.39 2008-11-21 10:21:31 67.195.37.200 then i said in the date of 21, there are 3 visitors come to my website and finnaly, i said in two days of the 20 and 21, there are 6 visitors come to my website and i use the following query, i just got 5 visitors, i could not get 6 visitors from the following query, could you tell why SELECT count(distinct visitor_ip) FROM `visitors_table` where visitor_date>="2008-11-20"
VIEWS ON THIS POST

224

Posted on:

Monday 29th October 2012
View Replies!

Help with charsets/Collation

I have a database of words that includes many names that contain accents, including accents on almost any letter. For instance: \trr \telry tefan eljko What charset or collation should I be using I'm using latin1_swedish_ci right now and it's giving me some problems, specifically at least with these characters, replacing them with question marks. It handles others fine. ş ě đ ł ,
VIEWS ON THIS POST

87

Posted on:

Monday 29th October 2012
View Replies!

Using limit to display second half of a table

Is there a way to just display the second half of a table I know you can statically set how many rows/offset with LIMIT, but can it be dynamic TIA
VIEWS ON THIS POST

188

Posted on:

Monday 29th October 2012
View Replies!

Distinct query...

, I am currently facing a prob I cannot solve on my own... I know it should have to do something with "distinct" but I dont know at all how to use it a proper form... I got a table with lotsa rows and want to loose some duplicates... now the problem is, that I need to specify only based on one column wether a row is a duplicate or not, while the other columns in the table could still differ even though the row would be a duplicate. I guess its easier to understand by an example: This would be my table id - column2 - column 3 1 - basis - 2364 2 - love - 2314 3 - loVe - 45987 4 - single - 4621 5 - net - 34234 6 - sIngle - 32432 So in this case I would want to loose the rows with the id 3 and 6. Does anyone know how to do that
VIEWS ON THIS POST

110

Posted on:

Monday 29th October 2012
View Replies!

Converting UTC to local time zones

I store hourly data in UTC DATETIME format. I'm trying to return the results in the local time zone for specified location (this one being in -06:00). I'm a little new to using mysql functions, first off it returns the 26th twice, and second.. the data is wrong. What am I missing Code: mysql> select `snapshot` -> , min(temp_f) as min_temp -> , max(temp_f) as max_temp -> from KDFW -> where `snapshot` -> between date_sub(UTC_TIMESTAMP(),interval 6 day) -> and UTC_TIMESTAMP() -> group -> by DATE(CONVERT_TZ(snapshot,'-00:00','-06:00')) -> order by snapshot; +---------------------+----------+----------+ | snapshot | min_temp | max_temp | +---------------------+----------+----------+ | 2006-10-26 04:53:00 | 70 | 70 | | 2006-10-26 06:53:00 | 67 | 86 | | 2006-10-27 06:53:00 | 54 | 68 | | 2006-10-28 06:53:00 | 49 | 75 | | 2006-10-29 06:53:00 | 48 | 82 | | 2006-10-30 06:53:00 | 62 | 78 | | 2006-10-31 06:53:00 | 47 | 69 | +---------------------+----------+----------+ 7 rows in set (0.02 sec) Thank You
VIEWS ON THIS POST

79

Posted on:

Monday 29th October 2012
View Replies!

Some error inline 48 i cant figure out

select left(chargeDate,8) as date ,(case when cos = '287' then 'Fbc College' when cos = '288' then 'milton margai' when cos = '289' then 'Ipam College' when cos = '290' then 'Njala College' when cos = '291' then 'college of medicine' when cos = '292' then 'fbc college cug' when cos = '293' then 'milton margai college cug' when cos = '294' then 'ipam college cug' when cos = '295' then 'njalla college cug' when cos = '296' then 'college of medicine cug' when cos = '474' then 'liccsal' when cos = '475' then 'liccsal college cug' when cos = '479' then 'polytechnic college' when cos = '480' then 'Polytek College CUG' when cos = '484' then 'Evang College' when cos = '485' then 'Evang College Cug' when cos = '594' then 'Freetown College' when cos = '595' then 'Freetown College CUG' else 'UNKOWN' end) as Destination ,COUNT(1) as callCount ,COUNT(distinct accnum) as uniqueSubs ,sum(CHARGEDURATION)/60 as Engeneeringminutes , (case when cos = '287' then 'Fbc College' when cos = '288' then 'milton margai' when cos = '289' then 'Ipam College' when cos = '290' then 'Njala College' when cos = '291' then 'college of medicine' when cos = '292' then 'fbc college cug' when cos = '293' then 'milton margai college cug' when cos = '294' then 'ipam college cug' when cos = '295' then 'njalla college cug' when cos = '296' then 'college of medicine cug' when cos = '474' then 'liccsal' when cos = '475' then 'liccsal college cug' when cos = '479' then 'polytechnic college' when cos = '480' then 'Polytek College CUG' when cos = '484' then 'Evang College' when cos = '485' then 'Evang College Cug' when cos = '594' then 'Freetown College' when cos = '595' then 'Freetown College CUG' end) ) as billableMinutes ,sum(debitrate1) as unitsConsumed from july where type in (16,20) and debitrate1>0 and (case when cos = '287' then 'Fbc College' when cos = '288' then 'milton margai' when cos = '289' then 'Ipam College' when cos = '290' then 'Njala College' when cos = '291' then 'college of medicine' when cos = '292' then 'fbc college cug' when cos = '293' then 'milton margai college cug' when cos = '294' then 'ipam college cug' when cos = '295' then 'njala college cug' when cos = '296' then 'college of medicine cug' when cos = '474' then 'liccsal' when cos = '475' then 'liccsal college cug' when cos = '479' then 'polytechnic college' when cos = '480' then 'Polytek College CUG' when cos = '484' then 'Evang College' when cos = '485' then 'Evang College Cug' when cos = '594' then 'Freetown College' when cos = '595' then 'Freetown College CUG' else 'UNKOWN' end) 'UNKOWN' group by left(chargeDate,8) ,(case when cos = '287' then 'Fbc College' when cos = '288' then 'milton margai' when cos = '289' then 'Ipam College' when cos = '290' then 'Njala College' when cos = '291' then 'college of medicine' when cos = '292' then 'fbc college cug' when cos = '293' then 'milton margai college cug' when cos = '294' then 'ipam college cug' when cos = '295' then 'njalla college cug' when cos = '296' then 'college of medicine cug' when cos = '474' then 'liccsal' when cos = '475' then 'liccsal college cug' when cos = '479' then 'polytechnic college' when cos = '480' then 'Polytek College CUG' when cos = '484' then 'Evang College' when cos = '485' then 'Evang College Cug' when cos = '594' then 'Freetown College' when cos = '595' then 'Freetown College CUG' else 'UNKOWN' end) order by ,(case when cos = '287' then 'Fbc College' when cos = '288' then 'milton margai' when cos = '289' then 'Ipam College' when cos = '290' then 'Njala College' when cos = '291' then 'college of medicine' when cos = '292' then 'fbc college cug' when cos = '293' then 'milton margai college cug' when cos = '294' then 'ipam college cug' when cos = '295' then 'njalla college cug' when cos = '296' then 'college of medicine cug' when cos = '474' then 'liccsal' when cos = '475' then 'liccsal college cug' when cos = '479' then 'polytechnic college' when cos = '480' then 'Polytek College CUG' when cos = '484' then 'Evang College' when cos = '485' then 'Evang College Cug' when cos = '594' then 'Freetown College' when cos = '595' then 'Freetown College CUG' else 'UNKOWN' end) ,left(chargeDate,8) asc still unable to figure out where i went wrong
VIEWS ON THIS POST

194

Posted on:

Monday 29th October 2012
View Replies!

MySQL error database....?

I have just started using MySQL and would like to know if there is any MySQL error database around on the inernet, similar to the ones of Microsoft error dbs The reason that I'm asking for this is that the syntax results in the newer version is very different to the older ones. I have obtained a courseware which is based on the version 3.23.33 and the one installed on my computer is v5.0.56. In the older version, inproper input with the syntax of tinyint and int zerofill will not gernerate any errors but the software will autoadjust the input instead, whereas in the newer version, inproper input of the syntax will generate the errors of 1064, 1264 or 1364 etc. This is a great improvement in that the software application to the highly law regulated industries will be more secure such as in financial and LIMS sectors in which software autoadjustification should not be allowed. My unsatisfaction with the newer version is that it does not have error db for me to find a quick solution. Thankfully, the errors mentioned above is not difficult to fix up if one is a bit of computer literatured. However, I'm sure when I continue to learn the MySQL, I will reach the stage where I'm not be able to fix the errors without detailed references. I hope the MySQL nurds would be able to grasp my point
VIEWS ON THIS POST

119

Posted on:

Monday 29th October 2012
View Replies!

Search and Remove

Is it possible to verify or "normalize" a field within mySQL I have a field that stores a lot of phone numbers, but it's possible they can be any length. I want to just grab the area code and prefix for a search on what city and state they belong too, but it's possible there can be a +, 1, or 011 before the area code starts, so it doesn't seem possible to use SUBSTRING since it can start in more than one position.
VIEWS ON THIS POST

68

Posted on:

Monday 29th October 2012
View Replies!

Referential integrity ptoblem

hey , i have been using mysql for two years, but am trapped in this lil problem, please dont laugh what i wanna do is simply create two tables, such that a field in one table is a foreign key in the other. i.e. create table a ( val1 int primary key ) create table b ( val1 int, val2 int, primary key (val1), foriegn key (val2) references a(val1) ). what i want is that table b must not have any value in val2 that is not present in table a. and one a value from table a is deleted, all the corresponding values from table b are automatically deleted !!!
VIEWS ON THIS POST

51

Posted on:

Monday 29th October 2012
View Replies!

INSERT inserts wrong value

Hopefully someone can point me in the right direction here. As the title says, an incorrect value is being stored in my table when doing an SQL query. INSERT INTO picks (id,player_id,position) VALUES(3,20050919104352853297,1) None of those is the primary key, yet the middle number gets messed up when stored. The current field type is varchar(35), latin1_swedish_ci. The value that's being inserted is: 20050919104352854016, so just the last 4 numbers are changing. , Forgot to say
VIEWS ON THIS POST

112

Posted on:

Monday 29th October 2012
View Replies!

Updating with formatted SQL

Originally posted by : Simon (simonhewitt2000@hotmail.com)I run a small site with news posts that my members can add to, I am trying to implement an update feature, I have created a form which I populate with the news record required.But the text field that holds the news article needs to be able to contain any characters, how can I do this when I pull it from a at the moment my SQL looks like this below (with the error I am getting) can you helpUPDATE newsfile SET newsid = '13', newstitle = '', newsbody = 'Lo , Morg here. Soon as I find a chance to bug Mute a bit more, that piccy of the week should be changing (got a funny one of a pyro, lol). Btw, in case anyone is wondering, we don't have any more BWTFCL matches (bar the one this thursday) until early november - the reason for this is that alot of clans have dropped out of Div3, and just coincidentally, many of those dropouts were the clans we were schedueled to play in the near future. So hopefully Twinner will get us a couple of friendlies - there is one on the 26th for sure against Freakred's clan.', umail = 'pk1@iol.ie', uname = 'Morgoth', date = '31/10/2000 ',time = '5:13:56 PM' WHERE newstitle='' Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Lo , Morg here. Soon as I find a chance to bug Mute a bit more, that piccy of the week should be changing (got a funny one of a pyro, lol). Btw, in case anyone is wondering, we don't have any more BWTFCL matches (bar the one this thursday) until '. /updates/update2.asp, line 33
VIEWS ON THIS POST

77

Posted on:

Thursday 15th November 2012
View Replies!

formatting the date

Originally posted by : narmadawill u plse tell me how to get the time in sql server 7.0
VIEWS ON THIS POST

127

Posted on:

Tuesday 20th November 2012
View Replies!

Group by Date

I have a datetime field - with date and time data. But I want to group the results only by date portion of the field. The result should also convert it to mm/dd/yyyy format. Tried using Convert with 101 - but its not giving expected results. For example, the field has following data: 2008-02-26 11:40:50.000 2008-02-26 11:44:27.000 2008-02-26 11:44:27.000 2008-02-27 14:56:17.000 2008-02-27 14:56:26.000 So the result should display: Visited Date Count -------------------------------- 02/26/2008 3 02/27/2008 2 Any ideas
VIEWS ON THIS POST

118

Posted on:

Tuesday 20th November 2012
View Replies!

how to use connect string while connecting to informix database useing odbc

Originally posted by : Ram (rbsaroj@rediffmail.com)hello i am faceing problem in connectstringi am able to connect informix rdbms databse by useing odbc from visual basic . But i am not ableto connect to databse by useing same odbc connect string through my ASP page . Can anybody help me with sending exact syntax my email id is rbsaroj@rediffmail.co
VIEWS ON THIS POST

109

Posted on:

Tuesday 20th November 2012
View Replies!

CAN SOME ONE PL HELP

Originally posted by : Lalit Hira (prodes@vsnl.com)I need to store the path of the picture to be uploased through the form into the database hou do i do it \t
VIEWS ON THIS POST

51

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Security from ASP

Originally posted by : Bryan Avery (b_avery@yahoo.com)Does any one have any ASP for dealing with SQL Server Security.egAdding UsersRemoving UsersChanging RollsChanging your own Password
VIEWS ON THIS POST

38

Posted on:

Tuesday 20th November 2012
View Replies!

Max date

, How do you find the maximum date of a column Say; d_id | d_date 0 | 11/26/2006 1 | 01/02/2007 2 | 02/02/2007
VIEWS ON THIS POST

202

Posted on:

Monday 26th November 2012
View Replies!

order by an alias?

I have problems with the following sql query: SELECT c.CarNr, c.Make, c.OutTo, c.OutOn, (t.Date - c.OutOn)+1 AS Days, c.Cost, Days * c.Cost AS Total FROM Cars c, Today t WHERE c.OutOn null ORDER BY Total Asc I get the error: Too few parameters. Expected 1. If I try to order by c.Cost it works. Is there a problem with ordering on an alias It seems like it doesn't find it.. I hope someone can help me
VIEWS ON THIS POST

103

Posted on:

Monday 26th November 2012
View Replies!

Designer Tab in wamp

can you help me please where i can find the designer tab in phpmyadmin, i am using wampserver2.2...is the designer tab availabe in wamp Thank you in advance.
VIEWS ON THIS POST

152

Posted on:

Sunday 30th December 2012
View Replies!