[PHP-DB] load data infile -- problem
Helo everyone ! I find this problem and I think I am close to the solution but ... The question: I need to import a few thousand of record storee in a *.csv file on my local harddisk, with my php application, managing my mysql engine. So, in command line, all works fine, all records are correctly imported. command line code - mysql load data infile 'c:\\datastream\\import\import -into table iport - fields terminated by ';' - ignore 9 lines; - /command line code But in my php app, because I am confuse a bit (use of ['], [] and the definition of variables in mysql_query), I don't make this import. -- php code --- $a = 'c:\\\datastream\\\import\\\import'; $extra_arg = 'fields terminated by \';\' ignore 9 lines;'; $query = 'load data infile \''.$a.'\' into table import '.$extra_arg.''; $result = mysql_query ($query) or die_mysql (brExecutia comenzii i$query/i a esuat.br); -- /php code -- I get this output-error: === load data infile 'c:\\datastream\\import\\import' into table import fields terminated by ';' ignore 9 lines; === and the import doesn't occured. Where is located my mistake ? Thanks in advance. -- cu respect, [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] pg_connect gets fatal error
On Aug 14, 2005, at 2:16 PM, Jon Crump wrote: I'm guessing you're right, but it's not clear how I could tell. The output from ?php phpinfo() ? doesn't really help much unless I know what to look for. On the one hand it says that DBX support is enabled and PostgreSQL is one of the supported databases, on the other, MySQL support is described explicitly, as is ODBC, but there is no similar section for the other supported DBs. If php wasn't compiled with postgres support, do I have to recompile it? how? Apologies if these questions are naive, but I am a rank tyro in these matters. Look at the Configure Command in phpinfo(). It should have the flag --with-pgsql. One of the easiest ways to get PHP/PostgreSQL going on the Mac is to use this installer: http://www.entropy.ch/software/macosx/php/ John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT Performance and INDEXing
Hey, this is my first question. So if you could just reply to say it reached the php-db list, that would be terrific. Of course, answering the questions would be awesome as well. I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared with running SQL2 and SQL3 ? Before splitting the statements, I also tried from phpmyadmin (Check table, Aalyze table, Optimize table) and creating an INDEX on field2, but I noticed no performance increase. Before explicitly adding an INDEX, the space usage in phpmyadmin already showed Type:Index using several bytes. The table still has the INDEX I explicitly created. Can someone explain to me INDEXing ? I was thinking of field2 and field3 for an INDEX (since field3 holds a number from 1 - 14 and the composite key field1 field2 would be unique), but I seem to be home free already. I would just like to know why performance slowed and then why it improved with my solution. Regards. -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] load data infile -- problem
I could be 100% wrong on this, but I do not think that a command line statement can be executed through mysql_query() - try exec(). If I remember correctly mysql load data infile ... is not executed from within mysql, but at the command line. (Hint: look at the source for phpMyAdmin and copy how it is done.) Regards - Miles At 08:30 AM 8/18/2005, select.now wrote: Helo everyone ! I find this problem and I think I am close to the solution but ... The question: I need to import a few thousand of record storee in a *.csv file on my local harddisk, with my php application, managing my mysql engine. So, in command line, all works fine, all records are correctly imported. command line code - mysql load data infile 'c:\\datastream\\import\import -into table iport - fields terminated by ';' - ignore 9 lines; - /command line code But in my php app, because I am confuse a bit (use of ['], [] and the definition of variables in mysql_query), I don't make this import. -- php code --- $a = 'c:\\\datastream\\\import\\\import'; $extra_arg = 'fields terminated by \';\' ignore 9 lines;'; $query = 'load data infile \''.$a.'\' into table import '.$extra_arg.''; $result = mysql_query ($query) or die_mysql (brExecutia comenzii i$query/i a esuat.br); -- /php code -- I get this output-error: === load data infile 'c:\\datastream\\import\\import' into table import fields terminated by ';' ignore 9 lines; === and the import doesn't occured. Where is located my mistake ? Thanks in advance. -- cu respect, [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] load data infile -- problem
You can definitely do a load file from within the mysql client, so I'd guess you can do it through mysql_query, too. I'm wondering about the semi-colon within the query. Maybe it needs to be escaped, too. David I could be 100% wrong on this, but I do not think that a command line statement can be executed through mysql_query() - try exec(). If I remember correctly mysql load data infile ... is not executed from within mysql, but at the command line. (Hint: look at the source for phpMyAdmin and copy how it is done.) Regards - Miles At 08:30 AM 8/18/2005, select.now wrote: Helo everyone ! I find this problem and I think I am close to the solution but ... The question: I need to import a few thousand of record storee in a *.csv file on my local harddisk, with my php application, managing my mysql engine. So, in command line, all works fine, all records are correctly imported. command line code - mysql load data infile 'c:\\datastream\\import\import -into table iport - fields terminated by ';' - ignore 9 lines; - /command line code But in my php app, because I am confuse a bit (use of ['], [] and the definition of variables in mysql_query), I don't make this import. -- php code --- $a = 'c:\\\datastream\\\import\\\import'; $extra_arg = 'fields terminated by \';\' ignore 9 lines;'; $query = 'load data infile \''.$a.'\' into table import '.$extra_arg.''; $result = mysql_query ($query) or die_mysql (brExecutia comenzii i$query/i a esuat.br); -- /php code -- I get this output-error: === load data infile 'c:\\datastream\\import\\import' into table import fields terminated by ';' ignore 9 lines; === and the import doesn't occured. Where is located my mistake ? Thanks in advance. -- cu respect, [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] load data infile -- problem
is the file sitting on the same server as the db? Are the webserver and db on the same machine? If they are not, and the file uploaded to the webserver, the db sesrver machine won't have access to the file unless you ftp it over... You may want/need to investigate a more php based approach where you open the file and read it in, parsing thru it to access the data elements and running sql statements thru a loop Bastien From: [EMAIL PROTECTED] To: Miles Thompson [EMAIL PROTECTED] CC: php-db-list php-db@lists.php.net Subject: Re: [PHP-DB] load data infile -- problem Date: Thu, 18 Aug 2005 09:25:22 -0500 You can definitely do a load file from within the mysql client, so I'd guess you can do it through mysql_query, too. I'm wondering about the semi-colon within the query. Maybe it needs to be escaped, too. David I could be 100% wrong on this, but I do not think that a command line statement can be executed through mysql_query() - try exec(). If I remember correctly mysql load data infile ... is not executed from within mysql, but at the command line. (Hint: look at the source for phpMyAdmin and copy how it is done.) Regards - Miles At 08:30 AM 8/18/2005, select.now wrote: Helo everyone ! I find this problem and I think I am close to the solution but ... The question: I need to import a few thousand of record storee in a *.csv file on my local harddisk, with my php application, managing my mysql engine. So, in command line, all works fine, all records are correctly imported. command line code - mysql load data infile 'c:\\datastream\\import\import -into table iport - fields terminated by ';' - ignore 9 lines; - /command line code But in my php app, because I am confuse a bit (use of ['], [] and the definition of variables in mysql_query), I don't make this import. -- php code --- $a = 'c:\\\datastream\\\import\\\import'; $extra_arg = 'fields terminated by \';\' ignore 9 lines;'; $query = 'load data infile \''.$a.'\' into table import '.$extra_arg.''; $result = mysql_query ($query) or die_mysql (brExecutia comenzii i$query/i a esuat.br); -- /php code -- I get this output-error: === load data infile 'c:\\datastream\\import\\import' into table import fields terminated by ';' ignore 9 lines; === and the import doesn't occured. Where is located my mistake ? Thanks in advance. -- cu respect, [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT Performance and INDEXing
I think no one answered it because it doesn't make a whole lot of sense. Breaking a condition out into a second SQL statement would force the DB to rescan the table, so it should take longer rather than shorter. There's nothing suggesting that it's doing an internal self-join or other time-consuming function off the bat, which might explain it. Granted a second query would benefit from caching from the first to some degree. Can you run EXPLAIN on the first statement and post the results? -Micah On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote: Hey, this is my first question. So if you could just reply to say it reached the php-db list, that would be terrific. Of course, answering the questions would be awesome as well. I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared with running SQL2 and SQL3 ? Before splitting the statements, I also tried from phpmyadmin (Check table, Aalyze table, Optimize table) and creating an INDEX on field2, but I noticed no performance increase. Before explicitly adding an INDEX, the space usage in phpmyadmin already showed Type:Index using several bytes. The table still has the INDEX I explicitly created. Can someone explain to me INDEXing ? I was thinking of field2 and field3 for an INDEX (since field3 holds a number from 1 - 14 and the composite key field1 field2 would be unique), but I seem to be home free already. I would just like to know why performance slowed and then why it improved with my solution. Regards. -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT Performance and INDEXing
Thanks for your reply. Here are the results of EXPLAIN: EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = 1 OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1 SIMPLE theTable ALL PRIMARY,field2 NULL NULL NULL 6400 Using where; Using filesort === Here are the results of EXPLAIN on the separate statements: EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field1 =1 OR field1 =2 ORDER BY field1 ASC id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE theTable range PRIMARY PRIMARY 8 NULL 2 Using where -- EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 =1 ORDER BY field1 ASC idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1 SIMPLE theTable ref field2 field2 9 const 10 Using where; Using filesort - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Thursday, August 18, 2005 9:49 AM Subject: Re: [PHP-DB] SELECT Performance and INDEXing I think no one answered it because it doesn't make a whole lot of sense. Breaking a condition out into a second SQL statement would force the DB to rescan the table, so it should take longer rather than shorter. There's nothing suggesting that it's doing an internal self-join or other time-consuming function off the bat, which might explain it. Granted a second query would benefit from caching from the first to some degree. Can you run EXPLAIN on the first statement and post the results? -Micah On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote: Hey, this is my first question. So if you could just reply to say it reached the php-db list, that would be terrific. Of course, answering the questions would be awesome as well. I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared with running SQL2 and SQL3 ? Before splitting the statements, I also tried from phpmyadmin (Check table, Aalyze table, Optimize table) and creating an INDEX on field2, but I noticed no performance increase. Before explicitly adding an INDEX, the space usage in phpmyadmin already showed Type:Index using several bytes. The table still has the INDEX I explicitly created. Can someone explain to me INDEXing ? I was thinking of field2 and field3 for an INDEX (since field3 holds a number from 1 - 14 and the composite key field1 field2 would be unique), but I seem to be home free already. I would just like to know why performance slowed and then why it improved with my solution. Regards. -- -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT Performance and INDEXing
Okay, well, I guess that EXPLAINS it.. (bad joke.. sorry.) Not being a huge expert on the inner working on MySQL, I'm at a loss to explain why this is happening exactly, but it's clear that MySQL is choosing to do a filesort over 6400 rows with the first statement. This is probably due to it's inability to index based on your where statement. The other statements can index out much smaller result sets, then order then and deliver (8 10 respectively) therefore accomplishing much faster. What I would do at this point, is play with reordering, or rewriting the SQL for the first statement to see if that makes a difference. I know if JOIN statements, the order of the WHERE condition can make a huge difference. Here's an example that assumes Field1 will never be less than 1. This may be an incorrect assumption, but it illustrates my point: SELECT field1, field2, andUpToField10 FROM theTable WHERE field1 3 OR field2 = 1 ORDER BY field1 ASC -or- SELECT field1, field2, andUpToField10 FROM theTable WHERE field1 = 1 OR field1 = 2 OR field2 = 1 ORDER BY field1 ASC (this is just subtly different, but I'd be curious if it affects the outcome) Also, add an index on field1 and field2 if you don't already, as that may help. (sometimes it doesn't, depending on data type) I hope that helps, -Micah On Thursday 18 August 2005 4:30 pm, Dwight Altman wrote: Thanks for your reply. Here are the results of EXPLAIN: EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = 1 OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1 SIMPLE theTable ALL PRIMARY,field2 NULL NULL NULL 6400 Using where; Using filesort === Here are the results of EXPLAIN on the separate statements: EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field1 =1 OR field1 =2 ORDER BY field1 ASC id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE theTable range PRIMARY PRIMARY 8 NULL 2 Using where --- --- EXPLAIN SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 =1 ORDER BY field1 ASC idselect_typetabletypepossible_keyskeykey_len refrowsExtra 1 SIMPLE theTable ref field2 field2 9 const 10 Using where; Using filesort - Original Message - From: Micah Stevens [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Thursday, August 18, 2005 9:49 AM Subject: Re: [PHP-DB] SELECT Performance and INDEXing I think no one answered it because it doesn't make a whole lot of sense. Breaking a condition out into a second SQL statement would force the DB to rescan the table, so it should take longer rather than shorter. There's nothing suggesting that it's doing an internal self-join or other time-consuming function off the bat, which might explain it. Granted a second query would benefit from caching from the first to some degree. Can you run EXPLAIN on the first statement and post the results? -Micah On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote: Hey, this is my first question. So if you could just reply to say it reached the php-db list, that would be terrific. Of course, answering the questions would be awesome as well. I meant 'Analyze table' and 'the composite key field2 field3 would be unique' - Original Message - From: Dwight Altman [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Wednesday, August 10, 2005 4:23 PM Subject: [PHP-DB] SELECT Performance and INDEXing I have a MyISAM table holding images with field types bigint(20), mediumblob, varchar(255), blob and tinyint(3). The table has grown to over 800 MB and over 6,000 rows. In the past week, performance has been about 15-20 seconds to run the following select statement which pulls only 16 maximum rows: SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2 = $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC Basically I always pull the first 2 images in the table via the primary key field1 and upto 14 additional images depending on a foreign key field2. field2 can have up to 14 repeated/duplicate entries. My working solution is that I have since split this into 2 select statements: SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1 = 1 OR field1 = 2 ORDER BY field1 ASC SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2 = $aField2Value ORDER BY field1 ASC and performance is back to instantaneous (as far as web responsiveness is concerned). Can someone explain why SQL1 took so long to run as compared with
[PHP-DB] Re: load data infile -- problem
select.now [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] So, in command line, all works fine, all records are correctly imported. command line code - mysql load data infile 'c:\\datastream\\import\import -into table iport - fields terminated by ';' - ignore 9 lines; - /command line code -- php code --- $a = 'c:\\\datastream\\\import\\\import'; $extra_arg = 'fields terminated by \';\' ignore 9 lines;'; $query = 'load data infile \''.$a.'\' into table import '.$extra_arg.''; $result = mysql_query ($query) or die_mysql (brExecutia comenzii i$query/i a esuat.br); -- /php code -- I get this output-error: === load data infile 'c:\\datastream\\import\\import' into table import fields terminated by ';' ignore 9 lines; === It looks like your slashes are a wee-bit messed up. Maybe try: $a = 'c:\\datastream\\import\\import'; Note: a single '\' means to escape the next character, so two '\\' means to put in a single '\'. You had three '\\\'. DanB -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SELECT Performance and INDEXing
No change on either variation. I also tried WHERE field2 = 1 OR field1 3
as well as omitting the ORDER BY clause.
Regarding INDEX: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html If a
multiple-column index exists on col1 and col2, the appropriate rows can be
fetched directly. If separate single-column indexes exist on col1 and col2,
the optimizer tries to find the most restrictive index by deciding which
index finds fewer rows and using that index to fetch the rows.
From the docs, I would have thought that an INDEX on field2 is all I needed,
but perhaps I need an index on (field1, field2) since they are both in the
where clause?
I didn't add the INDEX on field1. Would you explain a little more about
them? field1 is already a PRIMARY key and listed in the Indexes box in
phpmyadmin as Type:PRIMARY. I did create an INDEX on field2 and it is
listed in the Indexes box in phpmyadmin as Type:INDEX. But apparently they
are both indexes. Also, do you mean create a separate index for field1 or
some kind of composite index {i.e. multiple-column index} on (field1,
field2)? (field2, field3) would actually be a unique composite key, so I
was wondering if some type of composite index on (field2, field3) would be
better. Then again, any combination with field1 would also be unique.
- Original Message -
From: Micah Stevens [EMAIL PROTECTED]
To: php-db@lists.php.net
Sent: Thursday, August 18, 2005 12:33 PM
Subject: Re: [PHP-DB] SELECT Performance and INDEXing
Okay, well, I guess that EXPLAINS it.. (bad joke.. sorry.)
Not being a huge expert on the inner working on MySQL, I'm at a loss to
explain why this is happening exactly, but it's clear that MySQL is
choosing
to do a filesort over 6400 rows with the first statement. This is probably
due to it's inability to index based on your where statement.
The other statements can index out much smaller result sets, then order
then
and deliver (8 10 respectively) therefore accomplishing much faster.
What I would do at this point, is play with reordering, or rewriting the
SQL
for the first statement to see if that makes a difference. I know if JOIN
statements, the order of the WHERE condition can make a huge difference.
Here's an example that assumes Field1 will never be less than 1. This may
be
an incorrect assumption, but it illustrates my point:
SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 3
OR field2 = 1
ORDER BY field1 ASC
-or-
SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 = 1
OR field1 = 2
OR field2 = 1
ORDER BY field1 ASC
(this is just subtly different, but I'd be curious if it affects the
outcome)
Also, add an index on field1 and field2 if you don't already, as that may
help. (sometimes it doesn't, depending on data type)
I hope that helps,
-Micah
On Thursday 18 August 2005 4:30 pm, Dwight Altman wrote:
Thanks for your reply. Here are the results of EXPLAIN:
EXPLAIN SELECT field1, field2, andUpToField10
FROM theTable
WHERE field2 = 1
OR field1 = 1
OR field1 = 2
ORDER BY field1 ASC
idselect_typetabletypepossible_keyskeykey_len
refrowsExtra
1 SIMPLE theTable ALL PRIMARY,field2 NULL NULL NULL 6400 Using
where; Using filesort
===
Here are the results of EXPLAIN on the separate statements:
EXPLAIN SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 =1
OR field1 =2
ORDER BY field1 ASC
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE theTable range PRIMARY PRIMARY 8 NULL 2 Using where
---
--- EXPLAIN SELECT field1, field2,
andUpToField10
FROM theTable
WHERE field2 =1
ORDER BY field1 ASC
idselect_typetabletypepossible_keyskeykey_len
refrowsExtra
1 SIMPLE theTable ref field2 field2 9 const 10 Using where; Using
filesort
- Original Message -
From: Micah Stevens [EMAIL PROTECTED]
To: php-db@lists.php.net
Sent: Thursday, August 18, 2005 9:49 AM
Subject: Re: [PHP-DB] SELECT Performance and INDEXing
I think no one answered it because it doesn't make a whole lot of
sense.
Breaking a condition out into a second SQL statement would force the DB
to rescan the table, so it should take longer rather than shorter.
There's nothing suggesting that it's doing an internal self-join or
other
time-consuming function off the bat, which might explain it.
Granted a second query would benefit from caching from the first to
some
degree.
Can you run EXPLAIN on the first statement and post the results?
-Micah
On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote:
Hey, this is my first question. So if you could just reply to say it
reached the php-db list, that would be terrific. Of course, answering
the
questions would be awesome as well.
I
