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
Posted On: Thursday 25th of October 2012 10:39:58 PM Total Views:  256
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




how to write the sql command in mysql?

for example: the table named products there is a product name field in the table. i want to show some related items which matched by the knowed products's product name.($product_name) how to write the sql query command eg: if the knowed products's product name is True Blood Season 1 DVD i want to get all the product name which begins as True Blood Season.. if the knowed products's product name is 24 Hours Season 7 DVD i want to get all the product name which begins as 24 Hours Season..
VIEWS ON THIS POST

231

Posted on:

Thursday 25th October 2012
View Replies!

how to use SAAS(plans based tables) in mysql

all I am just implementing a SASS based database for a sample application. I have 3 plans and all three are different basic medium Advanced I need to develop a db tables in mysql for these 3 memberships I have different options in each plan How can I design the basic db and how can I know who is belongs to which plan using mysql Can anyone help me
VIEWS ON THIS POST

196

Posted on:

Thursday 25th October 2012
View Replies!

mysql how to create a dynamic table

. i have a problem that I am unable to resolve myself. I am building a jobs board for members to be able to communicate with each other. I anticipate that members will send out and receive a lot of correspondence between each other via the site. So i obviously need to have a database to store their communications with each other. My questions are What is the best way to approach this Do I dynamically create individuals table for each members ( i suspect this is the right approach). Or simple table for all the members ( i suspect that such a table will be too cumbersome and difficult to control in the long run. It would end up with millions of emails) . I tried to dynamically create a table for each member. The title for the dynamic table is member ( plus a concatenating of the $user_Id ( thus creating a unique table for each indivudial ). However when i tried to run it to the database, the mysql database did not create the tables. i have no idea where i have gone wrong with the SQL syntax. could someone please look at my SQL query and tell me if its correct. Thank you in advance for your kind help. PHP Code: require_once('databaseb_functions.php');//Connecttothedatabase. \t\t\t\t\t\t\t\t\t\t \t\t\t\t \t\t\t\t$sqlCommand\t="USEjobs; \t\t\t\t\t\t\t\tCREATETABLE\tmembers.'$id(\t\t \t\t\t\t\t\t\t\tuser_idint(11)NOTNULL, \t\t\t\t\t\t\t\tforeign_id\tint(11)NOTNULL, \t\t\t\t\t\t\t\tmessages_sentvarchar(255)NOTNULL, \t\t\t\t\t\t\t\tdate_sentvarchar(255)NOTNULL, \t\t\t\t\t\t\t\t\t\tmessage_received\tDATETIMENOTNULL, \t\t\t\t\t\t\t\tdate_received\tDATETIMENOTNULL,\t\t \t\t\t\t\t\t\t\t\t \t\t\t\t\t\t\t\t)"; \t\t\t\t\t\t\t\t\t \t\t\t\t\t\t\t\t\t\t \t\t$SqlCommand\t=mysqli_query($dbc,\t\t$sqlCommand);\t\t\t\t//Runthequery.
VIEWS ON THIS POST

158

Posted on:

Thursday 25th October 2012
View Replies!

I don't know query for this custom fetch from mysql

I don't know query for this custom selection from mysql. My question is similar to the thing as in PHP, we have a function isset($variable) and !empty($variable), is there anything similar in mysql because I want to fetch a set of records, where book_id is set or not left empty.
VIEWS ON THIS POST

119

Posted on:

Thursday 25th October 2012
View Replies!

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

last week I installed and successfully set up mysql on mac.. (10.6.6) was able to connect fine from unix shell and from PHP and JSP.. today, just a week later, I can't connect with either PHP or JSP, and when try to connect from unix shell I get this error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) what is this please... would appreciate some help...
VIEWS ON THIS POST

251

Posted on:

Thursday 25th October 2012
View Replies!

Import .xls file into a mysql database

Import .xls file into a mysql database I have a .xls file that i would like to import into a mysql database, I am ok creating the mysql table _temp in my webspace and try this code in mysql: Code: LOAD DATA INFILE 'D:/11_02_11.xls' INTO TABLE _temp FIELDS TERMINATED BY ',' IGNORE 1 LINES; But error: [Err] 1261 - Row 1 doesn't contain data for all columns Any ideas most welcome.
VIEWS ON THIS POST

178

Posted on:

Thursday 25th October 2012
View Replies!

mysqladmin -u root status; command not found

Im just starting with Kevin Yanks book. Im on OSX 10.4 so i installed MAMP. when i type in "mysqladmin -u root status" terminal responds: command not found.
VIEWS ON THIS POST

233

Posted on:

Thursday 25th October 2012
View Replies!

which mysql server to download??

hey friends...i request you all to pls help me out...im damn confused... im planning to make a website by March,2011 in which i will be using HTML/JSP and Mysql as d/b. however, i have a few doubts abt. Mysql.... 1) on the mysql site, there are many packages available to download. which one should i d/l. im using windows 7 and netbeans IDE 2) im confused whether i should use only the essentials package or the complete package wats d difference between both as far as query processing time is considered. my web application needs lowest query response time. 3) any tutorial site covering all the queries of Mysql....\t 4) is there any other d/b that i should use instead of Mysql im not willing to invest any money since im a student and i have previusly worked on oracle and sql. thanx a lot in advance.
VIEWS ON THIS POST

199

Posted on:

Thursday 25th October 2012
View Replies!

mysql privilege: does "select" allow me to join

After I changed my privileges to just select, this query ceased to function: Code SQL: SELECT base_SalesTable.* ,buckets.width ,buckets.teeth FROM base_SalesTable LEFT OUTER JOIN buckets ON base_SalesTable.name = buckets.name WHERE base_SalesTable.category = :category AND base_SalesTable.mincap = :mincap ORDER BY weight Happy Holidays
VIEWS ON THIS POST

153

Posted on:

Thursday 25th October 2012
View Replies!

Strange mysql query output from 3 table join

TableA (Author Table) author_id author_name TableB (Post Table) post_id author_id TableC (Earning Table) post_id (post id is not unique) post_earning I wanted to generate a report consists of per author total earning. author_id author_name total_earning (sum of earnings of all the posts by author) The SQL Query used: Code: SELECT a.author_id, a.author_name, sum(post_earning) as total_earnings FROM TableA a Inner Join TableB b on b.author_id = a.author_id Inner Join TableC c on c.post_id = b.post_id Group By a.author_id, a.author_name The Result I got is this : Code: ID user_login total_earnings 2 Redstar 13.99 7 Redleaf 980.18 10 topnhotnews 80.43 11 zmmishad 39.27 13 rashel 1248.34 14 coolsaint 1.66 16 hotnazmul 9.83 17 rubel 0.14 21 mahfuz1986 1.09 48 ripon 12.96 60 KHK 27.81 The sum of the total earning is actually 2863.22. But if i add all the values of the result table I get 2415. Where is the problem What I am missing I am sure SQL here can help me out. Here are the 3 tables i am using http://bit.ly/cNKS6k (TableA.csv) http://bit.ly/cJxRRN (TableB.csv) http://bit.ly/bNv9hs (TableC.csv)
VIEWS ON THIS POST

205

Posted on:

Thursday 25th October 2012
View Replies!

php mysql form date query help

php mysql form date query help hello all- I am trying to do a mysql database lookup by passing a begin date and end date from an html form. I have a date column in my database which is written to using the php function date("Y-m-d"); The date column is currently formatted as a mysql DATE field. The goal is to allow the user to lookup customer rows based between a begin and end date. Here is my string, which fails: $query='select * from '.$table.' where Date between `'.$Begin_Date.'` and `'.$End_Date.'` '; echo mysql_error(); the error message returns: Unknown column '2010-09-03' in 'where clause'
VIEWS ON THIS POST

246

Posted on:

Thursday 25th October 2012
View Replies!

How to connect to mysql remotely

I have a host running cpanel, and I have phpmyadmin. How do I connect to mysql remotely I would have a few websites tapped into one database... probably in a read only mode. Please point me in the right direction.
VIEWS ON THIS POST

318

Posted on:

Thursday 25th October 2012
View Replies!

mysql joining 2 tables (default values)

.. I came to situation where I have to join 2 tables on some other table: some_table: id domain table domains: id domaintype_id domain table domaintypes: id type This is how I join: domains ON some_table.domain = domains.domain domaintypes ON domaintype.id = domains.domaintype_id Now it happens that sometimes there is no domain for some_table row (so the domaintype will be NULL) and I want to be able to set this column default value 0 (when that happens).. How is it possible to do that Many
VIEWS ON THIS POST

238

Posted on:

Thursday 25th October 2012
View Replies!

mysql help, query hangs

SELECT a.id,a.dateid,a.ticker, case when a.close-(select b.close from hist b where a.ticker=b.ticker and b.dateid=a.dateid-1 ) > 0 then a.close-(select c.close from hist c where a.ticker=c.ticker and c.dateid=a.dateid-1 ) else '0.0' end from hist a where a.dateid > 3568;
VIEWS ON THIS POST

182

Posted on:

Thursday 25th October 2012
View Replies!

need help with simplifyling mysql queries.

ok im really not sure what this is called so im making a new thread. in my current website i need to select photos using 3 different mysql calls, is there a way to simplify them into one mysql call there are 3 types of photos i need selected and they are related to the current photo and "report" thing, so far i have been doing it this way: Code MySQL: "SELECT id FROM photos WHERE seq > :seq and reportid=:reportid and seq NOT LIKE '0' ORDER BY seq ASC LIMIT 1"; "SELECT id FROM photos WHERE id < :fotoid and reportid=:reportid ORDER BY id ASC LIMIT 1"; "SELECT id FROM photos WHERE seq=( SELECT MAX(seq) FROM photos WHERE reportid=:reportid) and reportid=:reportid ORDER BY ID ASC LIMIT 1"; but now i started wondering if its a bit excessive and could be possibly simplified into 1 mysql query
VIEWS ON THIS POST

254

Posted on:

Thursday 25th October 2012
View Replies!

Store array in mysql

Hi I have a table with a whole list of sites, and then each user can add those sites to his account, so basically i want to create a field in my users table that stores all the different sites that a person has added to their account. It's similar to facebook and adding friends... The best way i can think of to do this is storing the different id's of the sites in an array in one single field - don't know if this is possible or if there's a better way to do this Also, then how would I retrieve this list of sites in array form and then how would i add another to this list
VIEWS ON THIS POST

266

Posted on:

Thursday 25th October 2012
View Replies!

accidentally deleted mysql database and my database?

I think I may have deleted the mysql database as well as a practice database ("ijdb") that I just created. Here is what I'm being given as a result of my SHOW DATABASES command: Code: mysql> USE ijdb; ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'ijdb' mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) Right before this, I was trying to add data to a table within the "ijdb" database, and the data included several single and double quotes. I may have done something inadvertent involving quotes and escaping, because I couldn't get back to the [mysql>] command prompt. I closed down my Terminal window, tried to USE ijdb, then when I was given the error I entered the SHOW DATABASES command. If I bombed the mysql database is there anything I can do to recover it, or do I need to reinstall
VIEWS ON THIS POST

257

Posted on:

Thursday 25th October 2012
View Replies!

Replacing mysql value

So I am trying to update a table from a MYSQL database with values from another table. I want to replace the values for each row of Columns A and B in table 1 with the values for each row of Columns C and D in table 2. How do I do this
VIEWS ON THIS POST

270

Posted on:

Thursday 25th October 2012
View Replies!

mysql table design suggestion

Hi Please give me best suggestion. I am using mysql database. In one table daily i am going to insert 4-6 lac records.Half of the records are type 1 and other are type 2.There is one column which distingwishes record whether it is of type 1 or type 2. Before inserting these records i am truncating the table. Database have other table also. We are generating two report of type 1 and type2 using join query on other tables. Please let me know whether i should keep the same table for these 4-6 lac records of type 1 and type 2 or keep separate table of type 1 and type 2 . Using 2 separate , number of records are going to divide in two table 2-3 lac per table. Please give me the best suggestion.
VIEWS ON THIS POST

263

Posted on:

Thursday 25th October 2012
View Replies!

Moving data from one mysql database to another

, I have an access database that I converted to mysql this week. My new task is to combine some of these tables from the old database to the new database. The old system had 87 tables and the new database has 46. I have to move roughly 5 million rows of data but before that happens, I need to map the attributes first to the new database tables. The structues between the two are totally different. Can someone please give me some pointers Is there some sore of a mapping tool that I can use for this I'm up to my eyeballs in SQL right now.
VIEWS ON THIS POST

233

Posted on:

Thursday 25th October 2012
View Replies!