Is it possible to do count (*) when doing insert ... select ... query in mysql / php?
Is it possible to execute a simple count (*) query in a php script while another PHP script is doing an insert ... select ... query?
The situation is that I need to create a table with ~ 1M or more rows from another table, and when inserting, I don't want the user to feel like the page is freezing, so I am trying to update the score, but using select (*) count from the table when pasting the background, I only got 0 until the insert is complete.
So, is there a way to ask that MySQL returns a partial result first? Or is there a quick way to do a series of inserts with the data obtained from the previous select query, while having about the same performance as insert ... select ... query?
The environment is php4.3 and MySQL4.1.
source to share
If you do a single INSERT ... SELECT, then no, you cannot get intermediate results. It will actually be a Bad Thing, as users should never see the database in an intermediate state, showing only a partial result of a statement or transaction. Check out ACID for more information .
However, the MyISAM engine can play fast and fluently with this. I'm pretty sure I saw MyISAM commit some but not all of the rows from INSERT ... SELECT when I interrupted it part of the path. You haven't specified which engine your table is using.
source to share
Without sacrificing performance? Most likely not. With a little performance loss, perhaps ...
But why do you regularly create tables and insert millions of rows? If you only do this very rarely, can you just warn the administrator (apparently the only one who is allowed to do this) that this is taking a long time. If you do this all the time, are you really sure you are not doing it wrong?
source to share
I agree with Stein's comment that it is a red flag if you copy 1 million lines at a time in a PHP request.
I believe that most of the time people are trying to optimize SQL optimizations they can get much better performance and throughput if you approach the problem differently. SQL shouldn't be your bottleneck.
source to share
Other users cannot see the insert until it is committed. This is usually a good thing because it ensures that they cannot see the semi-assigned data. However, if you wanted them to see intermediate data, you could use a special call to commit on insert.
By the way, don't let anyone tell you you're turning on auto-message. This is a HUGE time. I have a "delete and reinsert" job in my database which takes 1/3 of the time when I turn off auto-update.
source to share
To be clear, MySQL 4 does not use transactions by default. It uses the MyISAM table type, which locks the entire table for every insert, if I remember correctly.
It is best to use one of the MySQL bulk insert functions, such as LOAD DATA INFILE , as they are significantly faster when inserting large amounts of data. As far as counting goes, you can break inserts into N groups of 1000 (or Y) and then split your progress bar into N sections and just update it on every group request.
Edit: One more thing to consider, if this is static data for a template, then you can use "select into" to create a new table with the same data. Not sure what your application is, or the intended functionality, but it might work.
source to share