Can some one help me? I am using mysql 4.0.20a
Here's the situation (I know it sounds kinda strange, but please follow
me here):
I'm trying to create a messaging forum. I have Forums (for example
sports), within those forums, I have topics, for example "Who will win
the World Series." There is another table which contains threads,
within the topics. I also have a hit_counter table which has an
object_id, object_type, and num_hits. I want to sort the results by
num_hits.
So, if I try to do something like this:
SELECT T.title, H.num_hits FROM topics as T, hit_counter as H, threads
as R WHERE T.forum_id=1 AND T.topic_id=R.to pic_id AND
H.object_id=R.t hread_id AND H.object_type=' THREAD'ORDER BY H.num_hits DESC
when I do an EXPLAIN SELECT it tells me:
Using where; Using temporary; Using filesort
which is SLOW.
Here's a short version of what I want to happen:
Topics picked by forum_id
Threads picked by thread.topic_id
hit_counter picked by thread_id AND
The whole thing sorted by hit_counter.num _hits.
Is there a way to do this any faster that doesn't sort a temporary file?
And I already have plenty indexes (I have one on forum_id, topic_id,
num_hits).
-d 1 2055
Hi Doug,
Maybe u should try to limit your query by adding : "LIMIT 0,50" (to
limit only 50 records) so that the data request should be smaller and
there is no more *SLOW* !
I hope it can help you.. :p
God Bless,
Octavianus S
Doug <do*****@XXXXre movetheXearthli nk.net> wrote in message news:<0q******* *********@newsr ead3.news.atl.e arthlink.net>.. . Can some one help me? I am using mysql 4.0.20a
Here's the situation (I know it sounds kinda strange, but please follow me here):
I'm trying to create a messaging forum. I have Forums (for example sports), within those forums, I have topics, for example "Who will win the World Series." There is another table which contains threads, within the topics. I also have a hit_counter table which has an object_id, object_type, and num_hits. I want to sort the results by num_hits.
So, if I try to do something like this:
SELECT T.title, H.num_hits FROM topics as T, hit_counter as H, threads as R WHERE T.forum_id=1 AND T.topic_id=R.to pic_id AND H.object_id=R.t hread_id AND H.object_type=' THREAD'ORDER BY H.num_hits DESC
when I do an EXPLAIN SELECT it tells me:
Using where; Using temporary; Using filesort
which is SLOW.
Here's a short version of what I want to happen:
Topics picked by forum_id Threads picked by thread.topic_id hit_counter picked by thread_id AND The whole thing sorted by hit_counter.num _hits.
Is there a way to do this any faster that doesn't sort a temporary file? And I already have plenty indexes (I have one on forum_id, topic_id, num_hits).
-d This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: alex |
last post by:
Hi. I am making a page that lots of data on the first page that I want links
displayed in a random order. If lots of people are looking at it, would I be
better using a page that uses arrays and shuffle(), or would I be ok to use
mysql.
If there are over a hundred visitors a day, would mysql be making the site
slow?
I don't know much about hardware, and if using mysql to get data for a front
page is too much, could it overload mysql?
...
|
by: Mike Chirico |
last post by:
Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004
The latest version of this document can be found at:
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
|
by: DJJ |
last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time to load making any kind linkage
with my Access data virtually useless.
I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting
out on a server and the Access database is running locally. The network
connection is very...
|
by: Gregory.Spencer |
last post by:
Hi there,
Using PHPMyAdmin and it is very usefully reporting problems with my
MySQL DB.
"PRIMARY and INDEX keys should not both be set for column
`column_name`"
and
|
by: Joshua D. Drake |
last post by:
Hello,
I think the below just about says it all:
http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg
Sincerely,
Joshua Drake
| |
by: jlee |
last post by:
I'm pretty much a newbie on mysql, and I need some help.
I am running mysql Ver 12.22 Distrib 4.0.24, for portbld-freebsd5.4
(i386) on a server hosting an active website.
The site's developer uses his own php shopping cart to receive customer
orders.
The configuration was done via cPanel with no external modifications -
which produced no protests when built, ran and connected with no
|
by: Ridge Burner |
last post by:
Can someone tell me which of these 2 SQL queries will be more efficient? I'm
having a debate with another guy about which would be less resource
intensive for MySQL.
The first uses MySQL to pick a random row in a single statement:
<?php
$sql = "SELECT FROM myTable ORDER BY RAND() LIMIT 1";
$query = mysql_query($sql,$conn);
?>
|
by: Ike |
last post by:
Recently, I began using a different MySQL verver (i.e. different machine as
well as different version#, going from 4.12a to 4.1.9 max).
The following query used to work:
select firstname, lastname, from associates where username like 'nancianne'
but now fails with:
"You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'from associates
|
by: Derrick Shields |
last post by:
I'm working with a database that has over 11 million rows. The .SQL
drop file is about 2.5gigs. Doing a simple query:
select * from people where last_name like '%smith%'
A query like this can take up to two minutes. I've spent hours reading
these forums and other sites on the internet, and I've figured that I
need to do a couple things:
create some indexes -and- possibly create some partitions
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |