[PHP-DB] [Fwd: Catalog listing]
LOL, look what I've got after posting to one of the groups about my problem with multiple JOIN query...Some kind of robot reads one of them? Original Message From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Precedence: bulk Subject: Catalog listing To: [EMAIL PROTECTED] Catalog PAGE OF THE BOOK IS VISIBLE IN THE SECTION does not exist. smime.p7s Description: S/MIME Cryptographic Signature
Re: [PHP-DB] Trouble With Counting New Documents With Complex Query
Jeremy Peterson wrote:
> I haven't tested this code, but it should get you started. I imagine that
> you have a lot of different days that you are trying to restrict in your
> query (This could be costing you a lot of time), so the first query removes
> all articles you don't want up front before doing your joins. If you were
> using a database that allows subselects, you could have shortened this even
> more (Last I heard MYSQL doesn't support subselects). Ultimatly you
> shorten the time the query executes because you are removing unnecessary
> computation with the joins.
>
> I hope this helps you along your way.
Thank you, I'll try to make something out of it
and test it :8] Maybe I will even get an idea or two
to improve it futher...
--
Seks, seksić, seksolatki... news:pl.soc.seks.moderowana
http://hyperreal.info { iWanToDie } WiNoNa) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne kształty... http://www.opera.com 007
smime.p7s
Description: S/MIME Cryptographic Signature
Re: [PHP-DB] Trouble With Counting New Documents With Complex Query
Jeremy Peterson wrote:
> Could you repost your original message (I deleted it already, sorry.) and
> more details about your database tables even some sample data. I'll try to
> make more sense of the query you are performing and help you along.
>
> In general it would be better to eliminate the joins. But there are other
> reasons to use the joins as you are doing, it just depends. Sometimes
> doing your distinct first then processing that data in an array will be
> faster. That is as long as you don't have a million records it needs to
> process.
>
> I'll see what I can do,
To put it short, this just takes to long
to process. I need to make it faster. Relevant
fragment below. What I want is a sum of all
"new", instantiated (visible) articles in all non
hidden sections, where "new" is defined as
"today, from 00:00:01 to 23:59:59". As you can
see I count in departments "2" and "5", because
other would give false results(the "3" department
shares a lot of articles with the "2" department
and if there is an article in the "3" department
it is always in "2" too). First I need to know
if the article is instantiated, so I JOIN with
x_instance. From the x_instance table I get also
the "state" of the article. Then I need to know the
department plus is the section I am querying visible at
all (some aren't), so I JOIN on x_section. Then
I add all the counts over all the sections and
pretty-print a sum. If you need some diagnostic
information, please tell me what you need (results
of EXPLAIN? SHOW?). IIRC, all the fields I JOIN on
are indexed (BTREE).
> $suma = 0;
> $pytanie = "SELECT COUNT(DISTINCT x_article.ID) AS CNT ";
> $pytanie .= "FROM x_article ";
> $pytanie .= "LEFT JOIN x_instance ";
> $pytanie .= "ON x_article.ID = x_instance.Article ";
> $pytanie .= "LEFT JOIN x_section ";
> $pytanie .= "ON x_instance.Section = x_section.ID ";
> $pytanie .= "WHERE (x_section.Status & 1) = 0 "; // not empty
> $pytanie .= "AND (x_section.Dept = 2 OR x_section.Dept = 5) "; // Drugs, NeuroGroove
> $pytanie .= "AND (x_instance.Status & 255) = 0 "; // not hidden, etc
> $pytanie .= "AND UNIX_TIMESTAMP(x_article.Date) BETWEEN " . mktime(0, 0, 1,
> date('m'), date('d'), date('Y')) . " AND UNIX_TIMESTAMP(NOW()) ";
> $pytanie .= "GROUP BY x_article.ID";
> $wynik = mysql_query($pytanie);
> while ($tmp = mysql_fetch_array($wynik))
> {
> $suma += $tmp['CNT'];
> }
> if ($suma)
> {
> // pretty-printing of the result
> $dzisdodano = str_pad((string)(int)$suma, 4, '0', STR_PAD_LEFT);
> }
> else $dzisdodano = '';
> ?>
>
> The table layout is as follows:
>
> mysql> DESC x_article;
> +-+--+--+-+--++
> | Field | Type | Null | Key | Default | Extra
> |
> +-+--+--+-+--++
> | ID | int(10) unsigned | | PRI | NULL |
> auto_increment |
> | Name| varchar(255) | YES | MUL | NULL |
> |
> | Description | varchar(255) | YES | | NULL |
> |
> | Keywords| varchar(255) | YES | | NULL |
> |
> | Content | mediumtext | | | |
> |
> | Date| datetime | | | 2001-01-01 00:00:00 |
> |
> | Author | varchar(100) | | | [EMAIL PROTECTED] ||
> | Feedback| varchar(100) | YES | | NULL |
> |
> | Size| int(32) | YES | | NULL |
> |
> | Words | int(32) | YES | | NULL |
> |
> | Images | int(32) | YES | | NULL |
> |
> +-+--+--+-+--++
>
> mysql> DESC x_instance;
> +--+--+--+-+-+---+
> | Field| Type | Null | Key | Default | Extra |
> +--+--+--+-+-+---+
> | Article | mediumint(9) | | MUL | 0 | |
> | Section | mediumint(9) | | MUL | 0 | |
> | Priority | tinyint(4) | | | 0 | |
> | Status | int(16) unsigned | | | 0 | |
> +--+--+--+-+-+---+
>
> mysql> DESC x_section;
> +--+--+--+-+---++
> | Field| Type | Null | Key | Default | Extra |
> +--+--+--+-+---++
> | ID | mediumint(9) | | PRI | NULL | auto_increment |
> | Name | varchar(100) | | M
Re: [PHP-DB] Trouble With Counting New Documents With Complex Query
Jeremy Peterson wrote:
> If you are concerned with speed, consider multiple queries. Joins cause
> most delays. Give that a shot.
What do you mean? Sorry, please explain
futher, I don't know what you have in mind...
I have to do some of those joins, because of
the relationships between tables - I don't
want to count invisible articles, so I have
to JOIN on x_instance table to get to know
if they are visible at all in any of the
subsections in the section tree. How would
you solve this dilemma? Thank you for your
time.
(sorry if my English is sometimes hard to understand)
--
Seks, seksić, seksolatki... news:pl.soc.seks.moderowana
http://hyperreal.info { iWanToDie } WiNoNa) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne kształty... http://www.opera.com 007
smime.p7s
Description: S/MIME Cryptographic Signature
[PHP-DB] Trouble With Counting New Documents With Complex Query
I need help width formulating the most
effective (in terms of processing time)
SQL query to count all the "new"
documents in the repository, where "new" is
defined as "from 00:00:01 up to 23:59:59
today". My current query does not give me
satisfactory results, it creates a visible
delay in rendering of the main page of one of
the departments (Drugs) :8[[[
(at least I, for now, think it's the culprit).
It's for the https://hyperreal.info >
site, see for yourself, notice the delay
https://hyperreal.info/drugs/go.to/index >.
Currently I ask MySQL to (offending
PHP fragment follows, I hope it is self-
explanatory).
The table layout is as follows:
mysql> DESC x_article;
+-+--+--+-+--++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+--++
| ID | int(10) unsigned | | PRI | NULL | auto_increment |
| Name| varchar(255) | YES | MUL | NULL ||
| Description | varchar(255) | YES | | NULL ||
| Keywords| varchar(255) | YES | | NULL ||
| Content | mediumtext | | | ||
| Date| datetime | | | 2001-01-01 00:00:00 ||
| Author | varchar(100) | | | [EMAIL PROTECTED] ||
| Feedback| varchar(100) | YES | | NULL ||
| Size| int(32) | YES | | NULL ||
| Words | int(32) | YES | | NULL ||
| Images | int(32) | YES | | NULL ||
+-+--+--+-+--++
mysql> DESC x_instance;
+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| Article | mediumint(9) | | MUL | 0 | |
| Section | mediumint(9) | | MUL | 0 | |
| Priority | tinyint(4) | | | 0 | |
| Status | int(16) unsigned | | | 0 | |
+--+--+--+-+-+---+
mysql> DESC x_section;
+--+--+--+-+---++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+---++
| ID | mediumint(9) | | PRI | NULL | auto_increment |
| Name | varchar(100) | | MUL | ||
| Parent | mediumint(9) | | MUL | 0 ||
| Dept | smallint(6) | | MUL | 0 ||
| Priority | tinyint(4) | | | 3 ||
| Keywords | varchar(255) | YES | | NULL ||
| Sorting | varchar(255) | | | Priority DESC ||
| OrderBy | varchar(255) | YES | | NULL ||
| SplitAt | smallint(5) unsigned | | | 25||
| Status | int(16) unsigned | | | 0 ||
+--+--+--+-+---++
Tell me if you need any additional information.
Thank you for all your help.
MySQL version is 4.0.17, PLD Linux Distribution MySQL RPM.
--
Seks, seksić, seksolatki... news:pl.soc.seks.moderowana
http://hyperreal.info { iWanToDie } WiNoNa) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne kształty... http://www.opera.com 007
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] CREATE TABLE LIKE, error
"CPT John W. Holmes" wrote:
> From: "Adam i Agnieszka Gasiorowski FNORD" <[EMAIL PROTECTED]>
>
> > I cannot use this query
> >
> > CREATE TABLE table LIKE other_table;
> >
> > , which is supposed to create an
> > empty "clone" of the other_table named
> > table.
> > Was it added in some later MySQL version?
> > It's in the manual on mysql.com, bo no version info
> > available.
>
> Sure there is, you just had to keep reading.
>
> Quote:
> In MySQL 4.1, you can also use LIKE to create a table based on the
> definition of another table, including any column attributes and indexes the
> original table has:
Ah, I missed it. Do you know what is
an estimate for 4.1 to go "stable"?
> CREATE TABLE new_tbl LIKE orig_tbl;
> CREATE TABLE ... LIKE does not copy any DATA DIRECTORY or INDEX DIRECTORY
> table options that were specified for the original table.
>
> Since this is a PHP list, an alternative, two-step method is to issue a SHOW
> CREATE TABLE Table_Name query, retrieve the results, and use them to create
> your second table (replacing the table name, of course).
Thank you very much.
--
Seks, seksić, seksolatki... news:pl.soc.seks.moderowana
http://hyperreal.info { iWanToDie } WiNoNa) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne kształty... http://www.opera.com 007
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] CREATE TABLE LIKE, error
I cannot use this query
CREATE TABLE table LIKE other_table;
, which is supposed to create an
empty "clone" of the other_table named
table.
Was it added in some later MySQL version?
It's in the manual on mysql.com, bo no version info
available.
--
Seks, seksić, seksolatki... news:pl.soc.seks.moderowana
http://hyperreal.info { iWanToDie } WiNoNa) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne kształty... http://www.opera.com 007
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Optimal mySQL query for next prev page system
Kim Steinhaug wrote:
> Then I query the mySQL database again to get the final result set, which
> I return together
> with the buildt prev/next htmlkode
Instead of querying twice, use the
SQL_CALC_FOUND_ROWS parameter of SELECT
statement and then do a quick
SELECT HIGH_PRIORITY FOUND_ROWS();
, to get the number of found rows
(full number, ignoring LIMIT clause).
--
Seks, seksić, seksolatki... news:pl.soc.seks.moderowana
http://hyperreal.info { iWanToDie } WiNoNa) (
http://szatanowskie-ladacznice.0-700.pl foReVeR( * )
Poznaj jej zwiewne kształty... http://www.opera.com 007
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
